Calc sort messing up formulas?

English
, , ,
#1

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.

Calc messing up formulas
#2

Which LibO version? Which OS? Did you have a look at: When I sort selected cells the sort changes formulas in unselected cells ?

#3

Sorting messes them up in Google Sheets too.

#4

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

#5

I 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 change

#6

You should now be able to upload.

#7

Thanks, 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.

#11

There were a feature request FDO#81309 and a rejection in the form of an adverse feature request FDO#81633 which led to a controversy among developers (fought out in FDO#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.

Those who need former behaviour: Downgrade to an older version (even 3.6.7) from the download archive.

Editing:
The suggestion above is outdated, of course. Recent versions do it as @Triggerhousinggroup describes it in his comment.

#12

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

#8

Are your formulas getting messed up after a sort? If so it is likely Bug 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.

#9

Sample file in commented bug:
test.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.

#10

This 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…

Thanks!

#13

I 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!

#14

I still have the problem in 4.3.3.2 as installed in Ubuntu 14.04.

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.