Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 21 Jan 2017 23:18:38 +0100Calc: Add rows without breaking formulashttps://ask.libreoffice.org/en/question/85872/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?Sat, 21 Jan 2017 11:43:37 +0100https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/Answer by Lupp for <p>I have a spreadsheet with a column which I used as a row counter, it begins in row 2 and A2's formula is</p>
<blockquote>
<p>=IF(ISBLANK(A1);1;A1+1)</p>
</blockquote>
<p>I copied a2 to A3:a100 so that I have a clean numeration: 1 2 3 4 ....</p>
<p>(I have the file ready to upload, but sorry the forum doesn't let me upload it yet)</p>
<p>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:</p>
<ul>
<li>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)</li>
<li>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</li>
</ul>
<p>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)</p>
<p>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.</p>
<p>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?</p>
https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?answer=85876#post-id-85876There 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.)Sat, 21 Jan 2017 12:06:36 +0100https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?answer=85876#post-id-85876Comment by paolobenve for <p>There is no way (afaik) to insert rows filling down contents at the same time. <br>
Filling down manually in a case as yours requires to not only fill the inserted rows but also to <strong>copy (fill) down the original formula for one step more</strong>, overwriting the formula(e) automatically adapted in a way breaking the intended functinality. The subsequent formulae below will still work correctly. </p>
<p>(Try to avoid inserting of rows / columns into an already <em>working</em> sheet. As soon as the locked output range of an "array formula" is intersected it will fail anyway.)</p>
https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?comment=85878#post-id-85878some 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?Sat, 21 Jan 2017 13:33:43 +0100https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?comment=85878#post-id-85878Answer by newuser2017 for <p>I have a spreadsheet with a column which I used as a row counter, it begins in row 2 and A2's formula is</p>
<blockquote>
<p>=IF(ISBLANK(A1);1;A1+1)</p>
</blockquote>
<p>I copied a2 to A3:a100 so that I have a clean numeration: 1 2 3 4 ....</p>
<p>(I have the file ready to upload, but sorry the forum doesn't let me upload it yet)</p>
<p>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:</p>
<ul>
<li>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)</li>
<li>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</li>
</ul>
<p>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)</p>
<p>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.</p>
<p>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?</p>
https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?answer=85900#post-id-85900I had the same problem when I previously used OpenOffice.Org and came across this forum [post](https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=2350).
I have since switched to LibreOffice and it seems to work just fine.
Hope this helps.Sat, 21 Jan 2017 23:18:38 +0100https://ask.libreoffice.org/en/question/85872/calc-add-rows-without-breaking-formulas/?answer=85900#post-id-85900