@ > Home > Contents > Inserted/deleted row breaks formula Excel 4+, SpreadCE, Ooo, Lotus


In D4 is a running sum =D3+C4 copied down. Inserting a new row, it changes to =D3+C5.

You copy D3: =D2+C3 down to D4: =D3+C4, thinking of being finished. But instead of being referred to inside D:D exactly once, D3 is twice (and D4 none). You can only avoid this by filling D3:D5 down, not only D3:D4.

Generations of tax consultants probably have needed tens of minutes to find the reason for resulting errors.


D4: Insert Name Define Name "Saldo" referring to: =D3+C4

(1) D4: =Saldo


(1) is no relative reference. It is a vector, like =Z[-1]S+ZS[-1]. 

Whatever happens, it always points to the two preceding cells. A relative reference changes its offset like an absolute reference when moving, inserting or deleting cells, as both keep the original link. Only when being copied, the vector and the relative reference behave alike.

See also the following "fake" vectors:

(3) D4: =OFFSET(C4,-1,1)+C4

(2) will not easily be discovered as a fake, since the relative one of the two references would not mind neither a row nor a column insert. It would not cause problems. (3) is really dangerous! Insert a column D - and get lost! The vector here is one from a preceding cell, not from itself.

Only a formula without cell arguments (like a named formula) can mirror e true vector:


Deciding between 8 or no needed functions will make everyone using (1).

Special purposes:

Internally =D3+C4 will convert to a local formula =Sheet1!D3+Sheet1!C4. You may define a global =!D3+!C4, but it does not work with VBA Calculate. To prevent from problems, you must redefine Saldo (and Saldo1 as a 2nd !Formula) strictly in order of calculation:

Sub Makro1()
    ActiveWorkbook.Names.Add Name:="Saldo", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="Saldo", RefersToR1C1:="=!RC[-1]+!R[-1]C"
    ActiveWorkbook.Names.Add Name:="Saldo1", RefersToR1C1:="=0"
    ActiveWorkbook.Names.Add Name:="Saldo1", RefersToR1C1:="=!RC[-2]+!RC[-1]+!R[-1]C"
End Sub