Delete a cell that is referred to in a formula without getting REF error?

[DannyJ]

Hi all,

I need to delete a cell that is referencfed in a formula. Please help!

B2 contains just the word 'Danny' B6 on a seperate worksheet contains =Plan!B2

Obviously when Idelete B2 the formula shows a REF error. I thought I would try $B$2 to stop the problem but this also doesn't work. Anything you could suggest would be much appreciated.

Thanks,

Danny

[xlnitwit]

Hi Danny,

Do you have to actually delete the cell rather than clearing its contents?

[TMS]

If you always want to refer to row 2 in column B, you could use INDEX(B:B,2).

Or, if Danny is a key field, you could use VLOOKUP or INDEX/MATCH to locate the correct entry.

[Tony Valko]

Maybe this...

=INDIRECT("Plan!B2")

That will ALWAYS refer to Plan!B2.