# Calc: Add rows without breaking formulas

I have a spreadsheet with a column which I used as a row counter, it begins in row 2 and A2's formula is

=IF(ISBLANK(A1);1;A1+1)

I copied a2 to A3:a100 so that I have a clean numeration: 1 2 3 4 ....

(I have the file ready to upload, but sorry the forum doesn't let me upload it yet)

I need to add a row in between, say between a5 and a6, in a way that preserve my numeration, i.e. I want to mantain the numeration, but whatever way I do the formula is broken:

• I imagine that I can insert a blank row (from contestual menu) and then copy a cell from the column to it, but this way a6 will become a7, but its formula will not change: IF(ISBLANK(A5);1;A5+1)
• I imagine that I can use the feature to copy a row inserting it, with ctl-alt-drag, but again: I select row 5, ctl-alt-drag it to row 6, but this way a6 and a7 have the same formula: IF(ISBLANK(A5);1;A5+1), and, obviously, numeration is broken

Deleting a row breaks the numeration, too: deleting row 6, the formulas of the new a6 are broken: IF(ISBLANK(A#REF!);1;A#REF!+1)

My real use case is more complex, I have other columns, and it's not a simple numeration, but the formulas involve more cells. However I have reduced the problem to this simpler form.

My question is: is there a way to insert or delete a row in a way that the numeration like the one I have are preserved?

edit retag close merge delete

Sort by » oldest newest most voted

There is no way (afaik) to insert rows filling down contents at the same time.
Filling down manually in a case as yours requires to not only fill the inserted rows but also to copy (fill) down the original formula for one step more, overwriting the formula(e) automatically adapted in a way breaking the intended functinality. The subsequent formulae below will still work correctly.

(Try to avoid inserting of rows / columns into an already working sheet. As soon as the locked output range of an "array formula" is intersected it will fail anyway.)

more

some earlier libre/open-office version had a feature to write a formula in a cell, and then to copy it to adjacent cells telling libre/open-office to keep it syncronized with the original cell... I was looking for this feature in order to check if it solved my problem, but I can't find it... is it still there?

( 2017-01-21 13:33:43 +0200 )edit

I had the same problem when I previously used OpenOffice.Org and came across this forum post.

I have since switched to LibreOffice and it seems to work just fine.

Hope this helps.

more