Preserving Formula

[Erika Schwartz]

I need to save a worksheet with formulas in some of the cells that
refer to a "sheet1" that actually won't exist until later. When I
create the worksheet with an existing sheet1 and then delete sheet1,
the cells return the reference error message. However, when I add
back sheet1 later, the original formula doesn't reappear. Is there
any way to preserve the formula so it reappears correctly when I add a
new sheet1?

 

[Frank Kabel]

Hi
AFAIK this is not possible

 

[Dee]

Save your worksheet with formulas that refer to sheetXYZ
and have that as a blank worksheet in your workbook. When
you're ready, move in sheet1. Then, do a find/replace:
find sheetXYZ and replace with sheet1.

 

[steveEX]

Actually, I think it is possible. Try this formula (for instance, if you
want to sum cells A1 to A4 on Sheet1)

=SUM(INDIRECT("Sheet1!A1:A4"))

The result will be an error if Sheet1 is deleted but if you add a sheet
named Sheet1 later, it will return the correct result.

steveEx