Formula for a value when the tab is not yet present Hi, I have a workbook with a
ID: 638597 • Letter: F
Question
Formula for a value when the tab is not yet present
Hi,
I have a workbook with a summary tab that pulls data from another tab in the workbook. The name of the second tab changes monthly (January, February, etc.). The graph on the summary tab uses the data from a table which pulls data from the second tab whose name changes each month. I would like to set the table up to pull data from the second tab but am having difficulty with the tab name change. Specifically if February hasn't occurred yet then there is no February tab for the data to pull from. Is there a way to enter a formula to pull data from a tab that doesn't exist yet without returning a #REF! and disturbing the graph??
Thanks! !
Explanation / Answer
Hi..
You could perhaps use the IFERROR() function to 'wrap' your existing formula in and return a value of zero (or anything you want).
Let us say you want the formula
=February!A1
but because the February sheet doesn't yet exist, you get the #REF! error. You could write the formula like this:
=IFERROR(February!A1,0)
Now, you still get the prompt to identify where February is, but now instead of #REF! you see 0. The IFERROR() says "if this formula creates an error, return the optional value otherwise return the results of the formula". This only works in Excel 2007 and later, if you're using 2003 or earlier, a different form of the formula would have to be used.
Testing here shows me that the formula still won't automatically recognize the February sheet when you do add it -- :(
But this will, but creates some maintenance headaches:
=IFERROR(INDIRECT("February!A1"),0)
Will show 0 until you create the February sheet, and when you do, it will automatically pick up the value from that sheet.
The problem is that because "February!A1" is a text literal, it doesn't update as you fill it up/down/left/right on the sheet. But if there's a pattern to using those and you do need to fill it we could do some math to 'fix' it.
For example, lets say that formula is on a sheet in row 2, we could write it this way:
=IFERROR(INDIRECT("FEBRUARY!A" & Row()-1), 0)
The ROW() would evaluate to a 2 (because the formula is in row 2, then 2-1=1 and it gets added on to FEBRUARY!A" to form the address FEBRUARY!A1
Hope this helps, and doesn't leave you feeling lost.