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?

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

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?

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.

I also would like to know if it is possible.

what you can do is in the meantime…

perhaps turn the A1 into $A1 in your formula? that will keep your numbers below the break point in order when adding rows.

I suggest assigning a shortcut for pasting formula only… then copy the formula and paste it at the press of the shortcut on the rows added…

Where there is a will, there is a way.

  • The ISBLANK() function will probably break your formula if you decide to put a header (column label) in cell A1. If you will be using that cell only to start the number sequence, you’ll be OK. Using ISNUMBER() (and reversing the logic) handles both cases.
  • Using OFFSET() from “this place” (current cell) will not “follow” references when you insert rows/columns, because the actual reference moves with the formula.
  • The ROW() function is another candidate to do the job.

Will you have blank rows in your final solution? Should a blank row be skipped in the count, continue the count, or restart? The approaches outlined above will behave differently in this respect. See attached file. Insert rows and see what happens.

Am I missing the point? Provide more info! A file with your data layout is probably the best way to show what you are working with.

I upvoted your question to raise your “karma level”, which should give you rights to upload. If you still can’t upload a sample, consider sharing it on a cloud service and posting a link here. Remember that this is a public place. Do not share a file containing confidential data.