| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average formula multiple worksheets | Excel Discussion (Misc queries) | |||
| Still trying to average 4 worksheets and having formula problems | Excel Discussion (Misc queries) | |||
| Basic Future Value Formula & Interest Rate Formula | Excel Discussion (Misc queries) | |||
| Determine Future Value to Meet Average | Excel Worksheet Functions | |||
| =Average(if( formula using different worksheets | Excel Worksheet Functions | |||
Hybrid Mode
