--Create a dynamic named range for the sheet names in H1 to Hn. Goto
InsertNameDefine
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


"Ted" wrote:

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)