How to get AVERAGE of 3 previous sheets automatically when you create a new sheet

[shahdelsol]

I am trying to get AVERAGE of 3 previous sheets of the same cells to the left of the last sheet on a workbook every time I am creating a new sheet. Can you do this by formula? I feel like you can't do this unless you manually update the formula each time you create a new sheet. Does anyone know if there is a way to do this by formula as it gets updated automatically when a new sheet created? If not how can this be done through VBA. Let's say I am looking for the average of L58 of the 3 previous sheets to the left of the last sheet.

I appreciate anyone who can help. ​

 

[shahdelsol]

This looks better however I tried it and I got (#value) error . Did it work on your end? I am just curious otherwise no big deal I can still used your original formula.

Glad we could help. I was playing around and found a shorter version of the B2 formula:

=AVERAGE(SUBTOTAL(1,INDIRECT("'"&TEXT(EOMONTH(DATEVALUE(1&A2),{-1,-2,-3}),"mmm yyyy")&"'!L58")))

 

[shahdelsol]

Please disregard. I had a typo and your shorter version of your formula works great. Thanks again!

This looks better however I tried it and I got (#value) error . Did it work on your end? I am just curious otherwise no big deal I can still used your original formula.