Cell Formula

[grahammal]

=IF('Form 1'!AI17="Yes","DATA")
Above is a formula that i'm using in a spreadsheet called 'Moves
Request Form' & works OK.
My problem is that sometimes I will be deleting 'Form 1' from my
workbook & generating it at another time.
When I do this the above formula reads =IF(#REF!AI17="Yes","DATA")
because 'Form 1' no longer exists.
How can I write the formula so that it retains the 'Form 1' entry even
when 'Form 1' dos'nt exist??

 

[Guest]

Some thoughts ...

Assuming that what you really want is just to "preserve" the orig. formula's
functionality, and that it's just a single cell formula, then perhaps we
could use INDIRECT, viz.:

=IF(INDIRECT("'Form 1'!AI17")="Yes","DATA","")

[I assumed you wanted it to return "blank" instead of FALSE, if it evaluates
to FALSE. Your original omitted the value_if_false]

When you delete: Form 1, the above will still return #REF! but it will work
again like magic when you "re-gen" a new sheet with the name: Form 1