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