#REF!

  • Thread starter Thread starter tb
  • Start date Start date
T

tb

Every time that I delete some rows, my formula gets messed up.

For instance, if my formula is =F2+G1 in column C3, F3+G2 in column C4
etc. etc. etc. and delete some rows (for instance row 5) then the
formula becomes =f4+#REF! in column C5.

What can be done to prevent that?
 
tb said:
Every time that I delete some rows, my formula gets messed up.
For instance, if my formula is =F2+G1 in column C3, F3+G2 in
column C4 etc. etc. etc. and delete some rows (for instance
row 5) then the formula becomes =f4+#REF! in column C5.
What can be done to prevent that?
Click to expand...

Correction: =#REF!+G4.

This happens because the formula in C6 was =F5+G4, and when you delete row
5, Excel does not know what to do with the reference to F5.

You can avoid the #REF error by using OFFSET and/or INDIRECT. But your
intent is unclear.

For example, if you want =F5+G4 to change to =F4+G3 -- that is, what was in
C5 before deleting the row -- put the following formula into C3 and copy
down:

=OFFSET(C3,-1,3)+OFFSET(C3,-2,4)

Caveat: OFFSET and INDIRECT are volatile functions. Formulas that use them
are recalculated every time any cell in any worksheet is edited. This can
slow editing operations greatly if you have a lot of volatile formulas.

Also, you will still get a #REF error if you remove row 1 or row 2.
 
Back
Top