Ask Your Question
2

Calc sort messing up formulas? [closed]

asked 2014-10-04 23:08:04 +0200

nurbles gravatar image

updated 2016-03-06 13:03:52 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-06 13:04:41.868851

Comments

Which LibO version? Which OS? Did you have a look at: http://ask.libreoffice.org/en/questio... ?

ROSt52 gravatar imageROSt52 ( 2014-10-05 03:13:09 +0200 )edit

Sorting messes them up in Google Sheets too.

rautamiekka gravatar imagerautamiekka ( 2014-10-05 12:38:32 +0200 )edit

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.

nurbles gravatar imagenurbles ( 2014-10-05 12:49:16 +0200 )edit

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

nurbles gravatar imagenurbles ( 2014-10-05 13:00:04 +0200 )edit

You should now be able to upload.

Lupp gravatar imageLupp ( 2014-10-09 12:59:05 +0200 )edit

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.

nurbles gravatar imagenurbles ( 2014-10-13 23:50:52 +0200 )edit

5 Answers

Sort by » oldest newest most voted
1

answered 2014-11-11 01:52:52 +0200

Lupp gravatar image

updated 2018-06-07 13:07:29 +0200

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.

edit flag offensive delete link more

Comments

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.

Triggerhousinggroup gravatar imageTriggerhousinggroup ( 2018-05-25 17:58:07 +0200 )edit
0

answered 2014-10-09 06:15:33 +0200

thelukester gravatar image

updated 2014-10-09 09:11:51 +0200

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.

edit flag offensive delete link more
0

answered 2014-11-11 00:32:04 +0200

Herberius gravatar image

updated 2014-11-11 00:35:28 +0200

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

example

Thanks!

edit flag offensive delete link more
0

answered 2014-10-09 22:16:35 +0200

m.a.riosv gravatar image

updated 2014-10-09 22:27:43 +0200

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.

edit flag offensive delete link more
-1

answered 2014-11-19 17:43:59 +0200

seanmadsen gravatar image

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!

edit flag offensive delete link more

Comments

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

aap gravatar imageaap ( 2014-12-15 00:07:23 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-10-04 23:08:04 +0200

Seen: 3,121 times

Last updated: Jun 07 '18