Increment reference sheet number in excel average formula

[chemhany]

I have a workbook of 30 worksheets in worksheet 31, I want to calculate the average for specific data from each worksheet. Simply I used the formula AVERAGE('1'!D$39:D$52). when I had dragged it down, the sheet number (which is 1) didn't change. Which formula can do that?

Thanks

 

[DRSteele]

Welcome to the forum.

This does what you ask. Note that the range d39:d52 in the formula is just text - it is used by function INDIRECT to complete the reference. Paste this into B8. Move it where you like, then drag it down 30 rows.

=AVERAGE(INDIRECT("'"&ROW(B8)-ROW(B$8)+1&"'!d39:d52"))

 

[chemhany]

Thanks a lot
That is work perfectly

 

[DRSteele]

You're welcome.

Do remember that the d39:d52 portion will not be dynamic.