#REF! error when deleting rows

[Greg N.]

Excel 2000, 9.0.4402 SR-1

Deleting a row that contains a referenced cell results in a #REF! error
at the referencing cell. But I think that's not how it's supposed to
work. Take this sample worksheet, consisting of 4 rows and 3 columns:

[1] =a2 =a3 =a4
[2] 111
[3] 222
[4] 333

When you enter the cell contents as shown above, the worksheet displays
this:

[1] 111 222 333
[2] 111
[3] 222
[4] 333

Now delete row [3]. This results in cell C1 being changed from "=a4" to
"=a3", and cell B1 being marked as in error. It displays like this:

[1] 111 $REF! 333
[2] 111
[3] 333

But that's not what the help text says. Unter the heading "Delete cells,
rows, or columns", it says:

"... Excel keeps formulas up to date by adjusting absolute references to
the shifted cells to reflect their new locations. However, a formula
that refers to a deleted cell displays the #REF! error value."

The help text talks about *absolute* references. This implies, that
relative references should be left alone. I would have expected this
result:

[1] 111 333
[2] 111
[3] 333

Is this a bug in the code, or in the help text? How can I achieve the
effect I want?

Greg

 

[Frank Kabel]

Hi
no this is not a bug (maybe the help file is not good at explaining
it...). You can prevent this with formulas such as
=INDIRECT("A2")
=INDIRECT("A3")
=INDIRECT("A4")

or use
=OFFSET($A$1,1,0)
=OFFSET($A$1,2,0)
=OFFSET($A$1,3,0)

 

[Greg N.]

... formulas such as
=INDIRECT("A2")

Perfect! Thanks.

Greg