Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 25 May 2018 17:58:07 +0200Calc sort messing up formulas?https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (**ALL** outside of the sorted block, but referring to things *inside* the block) stayed the same, as expected.
I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is **VERY** annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.
I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.Sat, 04 Oct 2014 23:08:04 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/Comment by nurbles for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40713#post-id-40713I just looked a little closer and discovered that it works fine sorting two rows that BOTH have values in them. When I included a third row that was blank, the formulas still ***LOOK*** correct, but the results are **WRONG**. For example, my sheet currently shows that 1695.73-863.78+0 is 1695.73. Pressing F9 does NOT fix it. However, replicating the [partial] row of formulas just above the sorted block, *through* the block fixes it. As far as I can tell, the formula text shown didn't changeSun, 05 Oct 2014 13:00:04 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40713#post-id-40713Comment by nurbles for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40711#post-id-40711The problem still exists in Version: 4.3.0.4 Build ID: 62ad5818884a2fc2e5780dd45466868d41009ec0, but was introduced at least one or two versions prior.
I just looked at the link from ROSt52 and it appears to be the exact same issue I have. It, too, has no answers. I was going to try to create an example, but I could not figure out how to attach it to my question.Sun, 05 Oct 2014 12:49:16 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40711#post-id-40711Comment by ROSt52 for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40694#post-id-40694Which LibO version? Which OS? Did you have a look at: http://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/ ?Sun, 05 Oct 2014 03:13:09 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40694#post-id-40694Comment by Lupp for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40883#post-id-40883You should now be able to upload.Thu, 09 Oct 2014 12:59:05 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40883#post-id-40883Comment by rautamiekka for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40710#post-id-40710Sorting messes them up in Google Sheets too.Sun, 05 Oct 2014 12:38:32 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=40710#post-id-40710Comment by nurbles for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=41128#post-id-41128Thanks, I found the 'add attachment' option if I edit my original question. However...
I'm having trouble generating a simple example. I found a block on my checkbook sheet where the problem occurs reliably (i.e. I change the sort key and sort again and the formulas still don't update, or update incorrectly) and copied it into a new sheet. In the new sheet, the sort always works perfectly.
Sigh. It would seem that for 2015, I must abandon my 22 yr old workbook (1 sheet/yr) and start over.Mon, 13 Oct 2014 23:50:52 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=41128#post-id-41128Answer by m.a.riosv for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40912#post-id-40912Sample file in commented bug:
[test.ods](/upfiles/14128857143479104.ods)
If someone is interested in a workaround:
If the formula is inside sorted range a formula like:
D4: `=OFFSET(D4;-1;0)+(C4-B4)`
after sort:
D3: `=OFFSET(D3;-1;0)+(C3-B3)`
works fine for me in the sample file.
If the formula is outside sorted range a formula like:
D4: `=OFFSET(INDIRECT("A"&ROW());-1;COLUMN()-1)+OFFSET(INDIRECT("A"&ROW());0;COLUMN(C1)-1)-OFFSET(INDIRECT("A"&ROW());0;COLUMN(B1)-1)`
doesn't change their result after sort, because although row number in COLUMN() can change, formula equally works.Thu, 09 Oct 2014 22:16:35 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40912#post-id-40912Answer by seanmadsen for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42726#post-id-42726I had this problem too and was also incredibly pissed off.
**Upgradding to 4.3.3.2 fixed it for me.** The default sorting has been restored to behaving the way it has been for years.
I can't believe the LibreOffice team let this happen. Thank goodness I had backups of my spreadsheets because this sorting issue sure mangled them! Wed, 19 Nov 2014 17:43:59 +0100https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42726#post-id-42726Comment by aap for <p>I had this problem too and was also incredibly pissed off. </p>
<p><strong>Upgradding to 4.3.3.2 fixed it for me.</strong> The default sorting has been restored to behaving the way it has been for years. </p>
<p>I can't believe the LibreOffice team let this happen. Thank goodness I had backups of my spreadsheets because this sorting issue sure mangled them! </p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=43679#post-id-43679I still have the problem in 4.3.3.2 as installed in Ubuntu 14.04.Mon, 15 Dec 2014 00:07:23 +0100https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=43679#post-id-43679Answer by Herberius for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42314#post-id-42314This bug appeared as fixed, nontheless I stil have the problem after applying all Calc updates... do you kno on which release the fix would be included?
Here is an example that shows what is going on...
![example](https://farm6.staticflickr.com/5604/15141346263_c5ce5d4785_o.jpg)
Thanks!Tue, 11 Nov 2014 00:32:04 +0100https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42314#post-id-42314Answer by thelukester for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40869#post-id-40869Are your formulas getting messed up after a sort? If so it is likely [Bug 81633](https://bugs.freedesktop.org/show_bug.cgi?id=81633) - Sorting shouldn't always automatically adjust references. If that's not your problem, please use the "attachment" button to provide us with a sample to reproduce this issue.Thu, 09 Oct 2014 06:15:33 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40869#post-id-40869Answer by Lupp for <p>I started keeping my checkbook register in Excel over 20 years ago. Several years ago I switched to OpenOffice and after a few years of that, to LibreOffice. Until recently, when I marked a block of cells to sort by the date and check number columns, the sort worked and the forumlas (<strong>ALL</strong> outside of the sorted block, but referring to things <em>inside</em> the block) stayed the same, as expected.</p>
<p>I'm not sure which, but one of the relatively recent updates to LibreOffice Calc has apparently started to update the formula references when I do the same sort I've been doing to over two decades. This is <strong>VERY</strong> annoying, because it can subtly introduce errors in my balance column. I can fix it by copying the formula cells just above the first row in sorted block throughout the sorted area, but I should not need to do that.</p>
<p>I've tried to find a setting that might (un)select this behavior, but I cannot. I'm hoping someone can tell me how to make this work correctly again, or that a developer will acknowledge that it is an unintended bug that will be fixed.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42318#post-id-42318There were a feature request [FDO#81309](https://bugs.freedesktop.org/show_bug.cgi?id=81309) and a rejection in the form of an adverse feature request [FDO#81633](https://bugs.freedesktop.org/show_bug.cgi?id=81633) which led to a controversy among developers (fought out in [FDO#85614](https://bugs.freedesktop.org/show_bug.cgi?id=85614) , e.g.). I've read a great deal of that and come to the conclusion: It is a complicated matter - and the most we we can state for sure might be "... that one man's feature is another man's bug", as Kohei Yoshida put it in the discussion.
The present state of the discusssion: V 4.4 will offer as an option if sorting shall adjust references or not.
The broken sheets suffer from having changed the default with V 4.2.(maybe? .7). I personally think it was a bad decission to change the default in this case. It could have been foreseen that a lot of documents would suffer from the change and possibly become unuseable. See [this comment](https://bugs.freedesktop.org/show_bug.cgi?id=81633#c30).
**Those who need former behaviour: Downgrade to an older version (even 3.6.7) from the [download archive](http://downloadarchive.documentfoundation.org/libreoffice/old/).**
Editing:
The suggestion above is outdated, of course. Recent versions do it as @Triggerhousinggroup describes it in his comment.Tue, 11 Nov 2014 01:52:52 +0100https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=42318#post-id-42318Comment by Triggerhousinggroup for <p>There were a feature request <a href="https://bugs.freedesktop.org/show_bug.cgi?id=81309">FDO#81309</a> and a rejection in the form of an adverse feature request <a href="https://bugs.freedesktop.org/show_bug.cgi?id=81633">FDO#81633</a> which led to a controversy among developers (fought out in <a href="https://bugs.freedesktop.org/show_bug.cgi?id=85614">FDO#85614</a> , e.g.). I've read a great deal of that and come to the conclusion: It is a complicated matter - and the most we we can state for sure might be "... that one man's feature is another man's bug", as Kohei Yoshida put it in the discussion. </p>
<p>The present state of the discusssion: V 4.4 will offer as an option if sorting shall adjust references or not. </p>
<p>The broken sheets suffer from having changed the default with V 4.2.(maybe? .7). I personally think it was a bad decission to change the default in this case. It could have been foreseen that a lot of documents would suffer from the change and possibly become unuseable. See <a href="https://bugs.freedesktop.org/show_bug.cgi?id=81633#c30">this comment</a>.</p>
<p><strong>Those who need former behaviour: Downgrade to an older version (even 3.6.7) from the <a href="http://downloadarchive.documentfoundation.org/libreoffice/old/">download archive</a>.</strong> </p>
<p>Editing: <br>
The suggestion above is outdated, of course. Recent versions do it as <a href="/en/users/28164/triggerhousinggroup/">@Triggerhousinggroup</a> describes it in his comment.</p>
https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=156029#post-id-156029Tools->Options->Calc->General->"Update Refs when Sorting" is the place to go in current versions to make it work how you want. The version I'm running, 6.0.4.2, unchecks this option by default.Fri, 25 May 2018 17:58:07 +0200https://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?comment=156029#post-id-156029