keep cell reference

[geebee]

hi,

i have a cell in sheet_new with a formula in cell A1:

= sheet1!C2

i would like to know how the cell A1 can keep this formula even if sheet1 is
deleted, because it may be possible to delete the sheet1 and later add it
back, so when it is added back, i want the formula remembered/restored.

thanks in advance,
geebee

 

[Jim Thomlinson]

This formula should work for you...
=IF(ISERROR(INDIRECT("Sheet1!A1")), "No Sheet1", INDIRECT("Sheet1!A1"))

 

[Gary Keramidas]

basically just set up a macro that adds a sheet and then the formula
maybe give some more info as to what you're doing, if this doesn't help

Sub Add_Sheet()
Worksheets.Add after:=Worksheets(Worksheets.Count)
Range("A1").Formula = "=Sheet1!C2"
End Sub

 

[Dave Peterson]

Unless sheet1!a1 contains an error <vbg>:

=IF(ISERROR(CELL("Address",INDIRECT("'Sheet1'!A1"))),
"No Sheet1","yourformulahere")

 

[geebee]

it is not working. nothing is happening. i even tried =INDIRECT(TEST1!F20)
and i get a #REF! error.

 

[Dave Peterson]

Watch your double quotes:

=indirect("test1!f20")