Bypassing Reference Error

[caseys_93]

I am creating a worksheet that needs to input information from other worksheets that have not been created yet. I will be using this to input information as time goes on (monthly); however when trying to create the cell, I get a message that says "a formula in this worksheet involves one or more invalid references. Verify that your formula contains a valid path, workbook, range name, and cell reference." The worksheets I am trying to reference will be created eventually, but I want a formula that will not have to be updated monthly and I can just use it to roll over month to month.

My current formula looks as such:
=IF((IF($A$2=1, '[2014 Corp Engineering Project Codes.xls]Period 1'!$A13, 0))(IF($A$2=2, '[2014 Corp Engineering Project Codes.xls]Period 2'!$A13, 0))(IF($A$2=3, '[2014 Corp Engineering Project Codes.xls]Period 3'!$A13, 0))(IF($A$2=4, '[2014 Corp Engineering Project Codes.xls]Period 4'!$A13, 0))(IF($A$2=5, '[2014 Corp Engineering Project Codes.xls]Period 5'!$A13, 0))(IF($A$2=6, '[2014 Corp Engineering Project Codes.xls]Period 6'!$A13, 0))(IF($A$2=7, '[2014 Corp Engineering Project Codes.xls]Period 7'!$A13, 0))(IF($A$2=8, '[2014 Corp Engineering Project Codes.xls]Period 8'!$A13, 0))(IF($A$2=9, '[2014 Corp Engineering Project Codes.xls]Period 9'!$A13, 0))(IF($A$2=10, '[2014 Corp Engineering Project Codes.xls]Period 10'!$A13, 0))(IF($A$2=11, '[2014 Corp Engineering Project Codes.xls]Period 11'!$A13, 0))(IF($A$2=12, '[2014 Corp Engineering Project Codes.xls]Period 12'!$A13, 0)))

I have up to 'Period 6' created so far, the error highlights 'Period 7'.

[daffodil11]

It will have the reference error as long as the reference doesn't exist. If you instead want to hide the error, you could encapsulate your formula in =IFERROR(formula,"") to show a blank until it actually holds non-error value. You'd still need a macro to activate each cell to get it to reevaluate.

INDIRECT is another option, as it circumvents the updating because it's volatile, but I wouldn't use more than a few dozen of those kinds of formulas as it will slow down performance. You'd still need to hide the INDIRECT in the IFERROR to mask the error, but at least INDIRECT will prevent the popups.

[MrShorty]

A couple of additional thoughts:

1)

I am creating a worksheet that needs to input information from other worksheets that have not been created yet.

Do you have to wait until period x passes before creating the spreadsheet? I have been known to create the spreadsheet long before it is needed just so I can program the spreadsheet without these kind of errors. The "future" spreadsheets can have something like N/A or "hasn't happened yet" or 0 or some other value in A13 that obviously means that it still is just a placeholder.

2) I notice that each conditional test in your formula is for A2=1 or 2 or 3 or 4 or .... For these kind of situations, I tend to make a lookup table and use a lookup function, instead of the long nested IF. That might be worth considering.

[TMS]

I'm not really sure what that formula is meant to do. If I take out the external workbook references, create sheets for each period, and spread the formula out, it doesn't seem to make sense.

You appear to have 12 individual self contained IF functions back to back:

IF($A$2=1, 'Period 1'!$A13, 0)

That says: "If cell A2 is 1, then use the value from cell A13 on worksheet Period 1, otherwise use the value zero (0)" ... times 12 periods.

=IF(

       (IF($A$2=1, 'Period 1'!$A13, 0))

       (IF($A$2=2, 'Period 2'!$A13, 0))

       (IF($A$2=3, 'Period 3'!$A13, 0))

       (IF($A$2=4, 'Period 4'!$A13, 0))

       (IF($A$2=5, 'Period 5'!$A13, 0))

       (IF($A$2=6, 'Period 6'!$A13, 0))

       (IF($A$2=7, 'Period 7'!$A13, 0))

       (IF($A$2=8, 'Period 8'!$A13, 0))

       (IF($A$2=9, 'Period 9'!$A13, 0))

       (IF($A$2=10, 'Period 10'!$A13, 0))

       (IF($A$2=11, 'Period 11'!$A13, 0))

       (IF($A$2=12, 'Period 12'!$A13, 0)))

I don't see how that's going to work.

I think you probably need something like:

=INDIRECT("'Period " & $A$2 & "'!$A13")

Just add the external workbook reference back in.


Regards, TMS