How to preserve a formula's reference to a worksheet when the worksheet is deleted and replaced?

Question

I am the author of an application that before opening an Excel workbook, deletes and recreates some worksheets, like, for example "Sheet1" in the example below.

Other worksheets (say Sheet2) in the same workbook may have formulas that refer to the replaced worksheet, like this:

=IF('Sheet1'!A9="","",'Sheet1'!A9)

Unfortunately, the reference in the above formula to Sheet1 is broken when the sheet is replaced, becoming

=IF(#Ref!A9="","",#REF!A9)

Can anyone think of a way to code this formula so it doesn't fail? It could, for example, get the name of the worksheet from a hidden cell in Sheet2 or something?

I just don't know enough about Excel formulas to know what the possibilities are.

TIA

Solution

Use Indirect Addressing and Named Ranges

1) Use phantom cells (i.e Z1...Zn) populated with the text "SheetN!" for each sheet you want to delete.

2) Define range names for all your externally addressable cells and ranges in those sheets

3) In your formulas replace the references with "Indirect" and the LITERAL of the named range.

Example:

Replace

    =Sum(Sheet1!A:A)   

with

    =SUM(INDIRECT(Z1&"caca"))  

Where "caca" is a Named Range defined in Sheet1 as A:A, and Z1 contains "Sheet1!"

Another Example:

Replace

    =Sheet1!A1+Sheet1!A2

with

    =INDIRECT(Z1&"NamedRangeForA1")+INDIRECT(Z1&"NamedRangeForA2")  

Now you can delete the Sheet1 without reference problems.

You should re-create (in VBA) the Named Ranges when you add your new Sheet1.

Edit: Answering your comment about Sheets with blanks in name

In this case you need extra-fu :)

Use this:

 =INDIRECT("_'_" & Z1& "_'_!_" & "caca")  

The "_" should be removed, as I inserted them there just for ease the visualization of the single and double quotes.

In the cell Z1 enter the name of Sheet1 without quotes and without exclamation mark (as it is already in the formula).

HTH!