Cell Formula

=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??

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"grahammal" wrote:
>
> =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??
>
>
> --
> grahammal
> ------------------------------------------------------------------------
> grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336
> View this thread: http://www.excelforum.com/showthread...hreadid=568919
>
>