Any reference in formula expressions expands automatically after insertion of new rows/columns.
There is one exception: If the reference includes only one row/column, nothing expands. SUM(A1:A99) does not expand when you insert an adjacent column, but SUM(A1:B99) does.
It not what i meant, lemme explain with example:
sheet1 have a named data range that goes R1C1:R30C10 at column C10 all rows have the same formula (=RC[-1]+RC[-2]),
so if i look at for example row 10 i expect and see that the cell R10C10 have the formula above.
Now when i insert a new text on the cell R31C1 which would be a new row, i would expect that the named data range would be updated to the new range: R1C1:R31C10 and that on the cell R31C10 the formula would be copied along
i would expect that the named data range would be updated to the new range:
R1C1:R31C10
Yep
and that on the cell
R31C10the formula would be copied along
No
https://forum.openoffice.org/en/forum/download/file.php?id=46813 performs both steps.
- Detects the current region of adjacent non-blank cells around the current range selection, so you just need to select some cell(s) in your list.
- Turns on “Expand references…” if it is turned off.
- Inserts as many rows into the current region as selected.
- Copies down any formulas from the row above.
- Selects remaining blank cells for editing.
- Turns off “Expand references…” if it was turned off.
A second macro deletes selected rows from the current region.