Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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 add a row in a way that the numeration like the one I have are preserved?

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 add a row in a way that the numeration like the one I have are preserved?

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 add insert or delete a row in a way that the numeration like the one I have are preserved?