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?