Average formula across future worksheets

[Ted]

I have an average formula =average(start:end!b37) that finds the average
across all the worksheets between the sheets named Start and End. However, I
use a macro which creates the End page when a user enters data. The formula
drops the "End" reference before the sheet is created. How can I lock the
formula so that it will always include "end"? (I am happy to get an error
message until the End page is created)

 

[Jacob Skaria]

--Create a dynamic named range for the sheet names in H1 to Hn. Goto
Insert>Name>Define
Name: mySheets
=OFFSET(Sheet1!$H$1,,,COUNTA(Sheet1!$H:$H))


=AVERAGE(N(INDIRECT("'"& mySheets &"'!A1")))

'handling error
=IF(ISERROR(AVERAGE(N(INDIRECT("'"& mySheets
&"'!A1")))),"",AVERAGE(N(INDIRECT("'"& mySheets &"'!A1"))))

If this post helps click Yes

 

[Jacob Skaria]

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

If this post helps click Yes