Bypassing Reference Error

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.

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.

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:

Formula:

Please Login or Register  to view this content.

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.

Formula:

Please Login or Register  to view this content.

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

I think you probably need something like:

Formula:

Please Login or Register  to view this content.

Just add the external workbook reference back in.


Regards, TMS