Average formula across future worksheets
 

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)

Average formula across future worksheets
 

--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)

Average formula across future worksheets
 

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

--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)