I have one workbook that consists of 64 worksheets, as follows: 1,2,3,....,31,Average,1 Ex,2 Ex,3 Ex,.....,31 Ex,Average Ex
In worksheet Average, I want to calculate the average for the previous 31 worksheets. In Average Ex, I want to calculate the average for the previous 31 Ex worksheets. In Average and Average EX sheets, there is a table of 31 rows. I have used the following formula in the first row that enables me to calculate the average for 31 sheets by scrolling down
=IFERROR(AVERAGE(INDIRECT("'"&ROW(B2)-ROW(B$2)+1&"'!G13:G56")),0)
The problem is in Average EX sheet; if I use that formula, it will refer to 1,2,3 .....,31 sheets not to 1 Ex,2 Ex,3 Ex,.....,31 Ex.
How can I modify it ??
Thanks !!
In worksheet Average, I want to calculate the average for the previous 31 worksheets. In Average Ex, I want to calculate the average for the previous 31 Ex worksheets. In Average and Average EX sheets, there is a table of 31 rows. I have used the following formula in the first row that enables me to calculate the average for 31 sheets by scrolling down
=IFERROR(AVERAGE(INDIRECT("'"&ROW(B2)-ROW(B$2)+1&"'!G13:G56")),0)
The problem is in Average EX sheet; if I use that formula, it will refer to 1,2,3 .....,31 sheets not to 1 Ex,2 Ex,3 Ex,.....,31 Ex.
How can I modify it ??
Thanks !!