Ask Your Question
0

Calc: Add rows without breaking formulas

asked 2017-01-21 11:43:37 +0200

paolobenve gravatar image

updated 2017-01-21 11:47:01 +0200

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-01-21 12:06:36 +0200

Lupp gravatar image

updated 2017-01-21 12:07:21 +0200

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

edit flag offensive delete link more

Comments

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?

paolobenve gravatar imagepaolobenve ( 2017-01-21 13:33:43 +0200 )edit
0

answered 2017-01-21 23:18:38 +0200

newuser2017 gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-01-21 11:43:37 +0200

Seen: 931 times

Last updated: Jan 21 '17