Automatically Update Last Row in Range Formulas

In OpenOffice Calc, worksheet formulas that apply a range of rows, such as SUM(), need to be updated when an extra row is added just above the cell with that formula. For example, if you start the following worksheet values and formulas ...

  A
1 4
2 7
3 9
4 =SUM(A1:A3)

... then when you insert a new row between rows 3 and 4, OO-Calc does not automatically update the second argument to the SUM() formula (Note: MS-Excel 2003 does). A solution is to use the OFFSET formula to automatically update the second argument like this: =SUM(A1:OFFSET(A4;-1;0;1;1)). In this example, the OFFSET returns a reference to a cell one row above cell A4.

See Also

• OFFSET Function