#REF! Problem

V

Voodoodan

Hi,

In a frozen pane, in cell X3, I have this formula: =$O$4+365

In cell O4 I have a date, ie., 09/09/2004
In cell O5 I have a date, ie., 13/09/2004

If I delete row 4, which brings row 5 up into 4, cell X3 now shows:

=#REF!+365


I would have thought, as there is still a value in O4, that the formul
would still remain correct?

Is there a way to keep the formula intact in X3, and prevent the #REF
error?

Thanks for any help,
Dan
 
J

JE McGimpsey

Since you deleted the referenced cell, you get the #REF! error (it's a
design decision - one can imagine situations where re-referencing to the
new O4 would cause undetected errors). One way to prevent this:

=INDIRECT("O4")+365
 
V

Voodoodan

Hi,

How odd, I was just looking through the formula listing and starte
working on 'indirect', but wasn't doing it properly!

Thanks for getting me there, I appreciate your help.

Dan
 
J

JE McGimpsey

Yup - the only thing I don't like about OFFSET() is that you run into
the same thing if row 3 is deleted. It's unlikely in this case, of
course.
 
A

Arvi Laanemets

No problems in this case at all - the formula on X3 is then gone too :))


Arvi Laanemets
 
Top