Bug 45146 - EDITING: references in formulas do not follow referenced values to new location during sort
Summary: EDITING: references in formulas do not follow referenced values to new locati...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: high enhancement
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
: 43004 62376 62490 (view as bug list)
Depends on:
Blocks: 85490
  Show dependency treegraph
 
Reported: 2012-01-23 12:17 UTC by andrea.gramegna
Modified: 2014-12-04 21:04 UTC (History)
14 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file (12.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-23 12:17 UTC, andrea.gramegna
Details
RainersSample (7.81 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-05-05 01:19 UTC, Rainer Bielefeld Retired
Details
test case showing simple formula becoming broken after simple sort (11.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-03-06 20:57 UTC, mohican
Details
file with 3 different behaviours when sorting (10.51 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-26 07:01 UTC, Winfried Donkers
Details
Test file to sort descending on column A (37.50 KB, application/xls)
2014-06-02 09:39 UTC, Laurent BP
Details
Demonstration of bug (96.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-17 19:41 UTC, helplibreoffice
Details
Comparision of Calc 4.4 vs Excel with Decending sorting A1:M500 (179.67 KB, image/png)
2014-10-28 02:21 UTC, Luke
Details
RainersSample comparison 4.4 vs 4.2 vs Excel (234.00 KB, image/png)
2014-10-28 04:04 UTC, Luke
Details

Note You need to log in before you can comment on or make changes to this bug.
Description andrea.gramegna 2012-01-23 12:17:37 UTC
Created attachment 56052 [details]
Example file

Problem description: 

Steps to reproduce:
1. If a cell have a complex formula like this: =E58*G58+E327*G327+E267*G267+E67*G67+E37*G37+I37+E188*G188+E263*G263+E251*G251+E246*G246+I246+4*G126+2*G155+E222*G222+I222+E137*G137
2. When ordered cells grow or descending by going to "Data", Sort..."
3. The result of the formula becomes wrong

In the attached file, the result of the formula in cell M28 is 320, when i order the column "I" the result in cell M28 becomes 25 and is incorrect.


Current behavior:
The result of the formula changes
Expected behavior:
The result of the formula does not change
Platform (if different from the browser): 
              
Browser: Opera/9.80 (Windows NT 6.1; U; Edition Campaign 21; it) Presto/2.10.229 Version/11.60
Comment 1 Rainer Bielefeld Retired 2012-05-05 01:18:18 UTC
I believe the problem is much more simple. Steps to reproduce:
1. open attached rainerssample.ods
   > in G7 you see a formula "=A1+A2+A3", in G8 "=SUM(A1:A3)"
2. select / highlight A1:A6
3. Menu 'Data -> Sort -> Top to bottom - Column A- Descending' <ok>
   > Sort order Column A now from 6 -> 1
   Expected: Values in G7,G8 still "6", formulas changed to 
             "=A6+A5+A4" and  "=SUM(A6:A4)"
   Actual: Both Cells show calculation result "15", formulas did not change

This never worked in a different way, I already see that in OOo 1.1.4
To me that is unexpected, but may be that is the intended behavior (for what reasons ever)? We need further research

@andrea.gramegna@gmail.com
Is my description concerning your intentions or did you want to report a different issue?
Can you please be more detailed and precise in your descriptions? The result of the caldulation does not "become wrong", but the result changes unexpectedly, ...
Comment 2 Rainer Bielefeld Retired 2012-05-05 01:19:13 UTC
Created attachment 61069 [details]
RainersSample

More simple sample document
Comment 3 mohican 2013-03-06 20:10:04 UTC
may be related to bug 38343
Comment 4 mohican 2013-03-06 20:57:02 UTC
Created attachment 76045 [details]
test case showing simple formula becoming broken after simple sort

I made this test case simplifying a Calc sheet of mine.

steps to reproduce :
1. select the lines that contains figures (rows 2 to 6)
2. sort on column B, reverse order
3. see how the formula in column C become broken because it looses its proper references


(currently using LibreOffice 4.0.1.1 on Ubuntu 12.04)
Comment 5 Joel Madero 2013-03-21 15:13:30 UTC
I vaguely remember this conversation happening in the past, I agree that it's not expected and IMO *should* be considered a bug as this could lead to miscalculations if the author is not aware of the weird behavior. That being said "logically" it might be called an enhancement if it's expected but then of course it'll get less attention.

IMHO we can mark as NEW and prioritize but Rainer I'll await your input from your research
Comment 6 Joel Madero 2013-03-28 18:30:44 UTC
Rainer -any update on this one?
Comment 7 Joel Madero 2013-04-06 19:23:19 UTC
I'm going to confirm this one, clearly not what is expected (not sure if this is by design or not, Rainer might find out some info about that).

I have been able to confirm the issue on:
Version 3.6.5.2 
Platform: Bodhi Linux 2.2 x64
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
As I've been able to confirm this problem on an earlier release I am changing the version number as version is the earliest version that we can confirm the bug, we use comments to say that the bug exists in newer versions as well.

Marking as:

New (confirmed)
Normal - can prevent professional quality work, can result in a user getting bad results without knowing. On more complicated spreadsheets could be a big concern.

High - quite a serious problem for anyone dealing with sorting + formulas


+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + 
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 8 Joel Madero 2013-04-16 23:14:51 UTC
*** Bug 62490 has been marked as a duplicate of this bug. ***
Comment 9 Joel Madero 2013-04-16 23:15:22 UTC
*** Bug 62376 has been marked as a duplicate of this bug. ***
Comment 10 Joel Madero 2013-05-15 02:29:07 UTC
*** Bug 38343 has been marked as a duplicate of this bug. ***
Comment 11 Joel Madero 2013-05-15 02:30:06 UTC
@Kohei - this one seems like a popular one, any chance you can take it on or maybe Markus? If not, any other ideas of someone who can poke at the code?
Comment 12 Joel Madero 2013-05-24 15:10:28 UTC
*** Bug 64647 has been marked as a duplicate of this bug. ***
Comment 13 Winfried Donkers 2013-08-26 06:53:24 UTC
*** Bug 43004 has been marked as a duplicate of this bug. ***
Comment 14 Winfried Donkers 2013-08-26 07:01:24 UTC
Created attachment 84629 [details]
file with 3 different behaviours when sorting

attached file shows 3 different behaviours, of which only one looks acceptable:

-open document
-step 1: sort A8:B13 on column B; A8:A13 now show wrong values
-step 2: sort A16:B21 on column B: A15:A21 keep the correct values
-step 3: undo step 1 and 2; sort A1:B5 on column B; both A8:A13 and A16:A21 now show wrong values
Comment 15 Winfried Donkers 2013-08-26 07:03:17 UTC
changed the version as this bug has always been present in LibreOffice
Comment 16 Winfried Donkers 2014-06-02 08:39:31 UTC
from bug 79441: 'It is broken from the old time, and none spreadsheet I tested (AOO, Excel) are able to fix it. I'm not sure it is possible.'

Excel does sort correctly provided the reference is on the same line (e.g. cell B4 contains '=B34'). It should be nice if at least that functionality could be fixed.
Comment 17 Laurent BP 2014-06-02 09:39:52 UTC
Created attachment 100278 [details]
Test file to sort descending on column A

(In reply to comment #16)
> from bug 79441: 'It is broken from the old time, and none spreadsheet I
> tested (AOO, Excel) are able to fix it. I'm not sure it is possible.'
> 
> Excel does sort correctly provided the reference is on the same line (e.g.
> cell B4 contains '=B34'). It should be nice if at least that functionality
> could be fixed.

I rebuild attachment 100089 [details] from scratch in Excel 2010 (see attached) and when sorting A15:C20 descending according to column A, column C is not sorted like in LibO.

Could you give a procedure which is correctly sorted in Excel and not in LibO?
Comment 18 Winfried Donkers 2014-06-02 10:21:36 UTC
(In reply to comment #17)
> I rebuild attachment 100089 [details] from scratch in Excel 2010 (see
> attached) and when sorting A15:C20 descending according to column A, column
> C is not sorted like in LibO.
> 
> Could you give a procedure which is correctly sorted in Excel and not in
> LibO?

I must apologise for any confusion I caused: I don't have Excel myself and relied on a collegue who does and who experiences the sorting problems regularly.
His information proved to be incomplete:
-when the reference is absolute and on the same row (attachment 100278 [details], range A24:C28) sorting goes well in Excel _and_ in Calc.
-when the reference is relative and on the same row (attachment 100278 [details], range A16:C20) sorting goes well in Excel with references to other sheets and wrong with references to the same sheet, and goes wrong in Calc.

Versions used here are Excel 2000 and LibreOffice 4.2.5.RC1, both with Windows7.

I hope my misinformation didn't cost you too much time. ;-)
Comment 19 Laurent BP 2014-06-02 10:48:44 UTC
(In reply to comment #18)
> -when the reference is absolute and on the same row (attachment 100278 [details]
> [details], range A24:C28) sorting goes well in Excel _and_ in Calc.
OK. No problem if references are absolute.

> -when the reference is relative and on the same row (attachment 100278 [details]
> [details], range A16:C20) sorting goes well in Excel with references to
> other sheets and wrong with references to the same sheet, and goes wrong in
> Calc.
> 
> Versions used here are Excel 2000 and LibreOffice 4.2.5.RC1, both with
> Windows7.
Sorting references to other sheet is broken since LibO4.2.5 RC1, but was OK before. See bug 79441 for that.

This bug report concerns only sorting internal references to the *same* sheet, which does not appear working in any spreadsheet. So I'm sure there is an easy solution for this bug.
Comment 20 Kohei Yoshida 2014-06-04 15:44:20 UTC
(In reply to comment #19)

> This bug report concerns only sorting internal references to the *same*
> sheet, which does not appear working in any spreadsheet. So I'm sure there
> is an easy solution for this bug.

Please stop insisting on this.  Internally the same mechanism is used both for references on the same sheet and other sheets.  They are caused by the same root cause!
Comment 21 Laurent BP 2014-06-04 17:18:21 UTC
(In reply to comment #20)
> (In reply to comment #19)
> 
> > This bug report concerns only sorting internal references to the *same*
> > sheet, which does not appear working in any spreadsheet. So I'm sure there
> > is an easy solution for this bug.
> 
> Please stop insisting on this.  Internally the same mechanism is used both
> for references on the same sheet and other sheets.  They are caused by the
> same root cause!

Sorry for the misunderstanding. I wanted to say exactly the opposite of what I wrote.
> > So I'm sure there
> > is NOT an easy solution for this bug.
Comment 22 helplibreoffice 2014-07-10 20:13:30 UTC
This is a severe bug because you wind up with a spreadsheet with the wrong results.  It makes the sort function largely useless.
Comment 23 Joel Madero 2014-07-10 20:22:47 UTC
Please don't change the priority/severity.
Comment 24 helplibreoffice 2014-07-11 02:17:43 UTC
Given that this results in spreadsheets with erroneous values, how does it become escalated to a higher priority/severity?
Comment 25 Joel Madero 2014-07-11 15:10:00 UTC
Bugs are fixed by volunteers. Priority/Severity is just used to give pointers to the developers but even if it's marked Critical - Highest, developers can choose not to fix it (this is a volunteer project and we don't push bugs on developers).

That being said, we try to stick with a workflow to keep priorities sane - this one I explained why it belongs where it does.

Normal - can prevent high quality/professional work
High - it's pretty serious (default is medium)

For reference: https://wiki.documentfoundation.org/images/0/06/Prioritizing_Bugs_Flowchart.jpg

Now this is border line "loss of data" but really it's not, the data isn't lost per say, it's changed (incorrectly) which is a normal bug.

Also - this has been around for years (since OOo days) and we have a total of 24 comments (many of them by the same users/QA staff), indicating that many people aren't seeing the problem.

Patches more than welcome if you want to have a go at it.

FWIW - Kohei already is aware of the bug and he's one of our Spreadsheet experts
Comment 26 Eike Rathke 2014-07-11 22:30:32 UTC
A relative in-sheet reference is supposed to point to a cell relative to the formula cell containing the reference and hence changes the value if the formula cell is moved during sort. If the reference shall stick to the same cell then an absolute reference must be used. This behavior is identical to how Excel treats it.

With the fix for bug 79441 the behavior now again is also identical for 3D references that include a sheet part, which makes the reference sticky just like in Excel.
Comment 27 helplibreoffice 2014-07-12 01:05:16 UTC
I've created an easily duplicated spreadsheet that illustrates what I think may be a problem.  I'm not sure if it matches the bug described in this (45156) or if it represents a different bug.  If it needs to be a separate bug, then I can create a new one.  Note that LO Calc may not be technically doing anything wrong, but it does not match user expectations, which is critical.

Create this simple spreadsheet, starting at cell A1:
2014-04-01   SubAssembly1    =B$2+B$3
2014-02-01   Nuts            10
2014-03-01   Bolts           5
2014-04-01   SubAssembly2    =B2+B3
2014-01-01   Grease          50
2014-05-01   Assembly1       =B4+B5
2014-05-01   Assembly2       =B$1+B$5

Now sort rows, using Column A as the key.  Notice the Error 522's.
Now undo, and sort rows using Column B as the key.  Notice the new Error 522's.
Now undo, and sort rows using Column C as the key.  Notice the new Error 522's.

In all these cases, the user had an expectation to be able to sort by any column without the data changing (with only the order of the rows changing).  This way, the spreadsheet can be sorted by date, part name, or cost.

When I add a fourth column that includes cells that add and subtract specific values from the third column, the problem gets even more pronounced.

Note that I intentionally used both absolute and relative references to cover both cases.  I had hoped at least one type of reference would work.  Obviously, only one type of reference would be used (this is just a demonstration).

Is this simple spreadsheet in error, or does it reflect a problem with the output of LO Calc not matching that of user expectations?
Comment 28 Joel Madero 2014-07-12 01:33:53 UTC
Do not reopen bugs that are closed by our most experienced developers.

Eike - if you want to reopen it please feel free to do so.
Comment 29 helplibreoffice 2014-07-12 04:37:20 UTC
Comments like that are why more people don't contribute to projects like LibreOffice.  

I took a significant amount of time out of my weekend to lend a hand, and received a slap on the wrist from a rude person as thanks.

How likely will I want to contribute more of my time or encourage others to contribute theirs?

I'm not an expert.  I'm an end user trying to help.  I found what looks like a bug and was instructed to post here.  As part of my post, there was a flag to reopen the bug, so I set it, as that seems like the most accurate status.

If you want this to be closed source and exclude others from contributing the best they can, then perhaps you could create a fork and request permission from all the contributors for a closed license.

If you want this to be a project built around a friendly community, make your actions in accordance with that goal.  Your post is in conflict with this community's goals of inclusion, equality, and openness.
Comment 30 Joel Madero 2014-07-12 04:54:43 UTC
I apologize if you mistook my "tone" - you're right I could have been more friendly. That being said - 

Eike is one of our most experienced developer, if he says it's not a bug, it's likely not a bug.

Then - if you're going to play around with the settings - please review our extensive documentation for how to do this. https://wiki.documentfoundation.org/QA/BugTriage. Reopened is not the correct status - I am moving this back to UNCONFIRMED - if Eike (or another developer who knows about things) thinks it should be set to NEW they can.

Then - in the future if you're not sure if you're reporting a bug or not (ie "Is this simple spreadsheet in error, or does it reflect a problem with the output of LO Calc not matching that of user expectations?") please go to the ask site or user thread first before coming to the bug tracker. 

Lastly - thanks for taking the time to report and for your patience.
Comment 31 helplibreoffice 2014-07-12 05:31:54 UTC
Thanks for your apology Joel.  Accepted.  And I apologize if I came across as too harsh.

This is a tricky bug and how to report it is tricky as well because the "correct" behavior here may be counter to the absolute logical behavior.  In other words, we must somehow allow the user to perform needed tasks.

How I discovered this bug (and apparently other people found it before myself) is that I created a spreadsheet with the intent of sorting it by different columns.  When I would re-sort it, the values would change.

Sometimes complicated spreadsheets contain errors, so I created a simple spreadsheet to demonstrate the problem.

From what I can tell, it is a very serious problem.  But I'm also very open to the possibility (and actually hoping) that I'm just not understanding how something works.  Being able to sort is important, and not having the values change during that sort is a basic requirement.

The ramifications is that my client has seen values change during the sort and wants to move the entire enterprise off of LibreOffice because she now considers it unreliable.  I am a huge proponent of LibreOffice and the sole reason why my clients use it.  As much as I love LibreOffice, I'm having a tough battle trying to convince them to stick with LibreOffice when it does not generate the results it seems like it should.

Two quick asides: please always try to remember that end users don't know names of the developers.  Also, I agree that this bug should be "Unconfirmed" until more comments are made.  Eike came to the conclusion that absolute references would solve the problem.  I was hoping he was correct, as that would be great.  But my example seems to show that absolute references don't solve it.  Unless the example is wrong.

The bottom line is that we need a way to sort while maintaining data and formula integrity.  At the least, this will be a documentation issue so users will know how to do it.  At the most, this will be a fairly important bug that will need a fix.  Anything that compromises the user's data is important.
Comment 32 Kohei Yoshida 2014-07-14 01:20:08 UTC
FYI, Bug 81309 might help.
Comment 33 Winfried Donkers 2014-07-14 16:20:21 UTC
Tested with master (4.4.0.0alpha+, build id f3d7734af45f30a87d6de76aa3de7593d541bdc8):

The steps described in description now produce 320 in M28 after sorting column I (correct, expected, result).

The steps described in comment 1 now produce 6 in G7 (correct, expected, result) and 15 in G8.

The steps described in comment 14 now produce correct, expected, results.

Setting this bug report to fixed.

Kohei, thank you :-)
Comment 34 helplibreoffice 2014-07-17 19:41:51 UTC
Created attachment 103002 [details]
Demonstration of bug

Sorting causes the formulas in the attached file to get messed up in v4.2.5.2.  Can someone test this in the new dev version that is supposed to work?

If LO crashes, that is from a different bug.  Delete all conditional formatting and try again.
Comment 35 helplibreoffice 2014-07-19 10:16:42 UTC
Is this still RESOLVED WORKSFORME, or should it marked be UNCONFIRMED?
Comment 36 Laurent BP 2014-08-11 16:12:32 UTC
Verified with versions:
- Version: 4.2.7.0.0+
Build ID: 8de2e9b4bc53e6c097897142bad223c100d36292
TinderBox: Win-x86@42, Branch:libreoffice-4-2, Time: 2014-08-08_21:42:21
- Version: 4.3.2.0.0+
Build ID: 4744400afc9c2be99f62b12180fa33b43acef564
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-08-11_06:41:42
- Version: 4.4.0.0.alpha0+
Build ID: ad85ce18d3939170e5ef7b1a1f8e5dda2c8aeb9c
TinderBox: Win-x86@39, Branch:master, Time: 2014-08-11_05:21:44
Comment 37 helplibreoffice 2014-08-20 16:41:22 UTC
@Laurent BP: To clarify, did you verify this bug, or verify that the functionality is correct?

How should the status be updated?
Comment 38 Laurent BP 2014-08-21 08:24:28 UTC
(In reply to comment #37)
> @Laurent BP: To clarify, did you verify this bug, or verify that the
> functionality is correct?
> 
> How should the status be updated?

I tested on the mentioned versions in comment #36 that references are correctly updated when sorted. Test with attachment 84629 [details] (comment #14) and attachment 100089 [details] (comment #16).
So bug status should be RESOLVED > FIXED.
However in these versions, sort seems broken for references to other sheet (bug 79441). So tests should be made on more recent build before closing the bug.
Comment 39 helplibreoffice 2014-08-21 16:23:52 UTC
While testing this issue, I found an unrelated issue (82915), and creating a separate bug report for that.  When doing so, I realized that the spreadsheet I posted in post #27 of this thread was not properly edited when I simplified it by hand for this bug report.

Here's how it should have looked:

2014-04-01   SubAssembly1    =C$2+C$3
2014-02-01   Nuts            10
2014-03-01   Bolts           5
2014-04-01   SubAssembly2    =C2+C3
2014-01-01   Grease          50
2014-05-01   Assembly1       =C4+C5
2014-05-01   Assembly2       =C$1+C$5

BTW, the example intentionally uses 2 different types of references for testing purposes of the bug reported in this thread.

If people can try sorting by columns A, B, and C to verify that LO Calc now produces expected results, that would be appreciated.

I only had time today to perform a quick test using v4.3.0.4, and it appeared to now work as expected.  This of course does not test any issues related to referencing other sheets as reported by others.
Comment 40 Jean-Baptiste Faure 2014-10-19 15:32:58 UTC
No commit notification for this bug report, then we do not know which commit fixed the problem. So the correct status is WorksForMe.

Best regards. JBF
Comment 41 Jean-Baptiste Faure 2014-10-19 19:05:27 UTC
This bug report is very confusing and disappointing because its description does not explain clearly and unambiguously what is the expected behavior and which scenario is tested.
If we take the description word for word, the expected result is that sorting should keep the value 320 in M28.
Ok, now do the following with LO 4.4.0.0.alpha+:

1/ select columns A to M, menu Data > Sort > Column I > Ok
Result: we still have 320 but in M32, in M28 we have nothing.

2/ select columns A to I, menu Data > Sort > Column I > Ok
Result: we still have 320 in M28 but 24 in A28, that is nothing in the same row as 28 in column A

3/ select columns E to I, menu Data > Sort > Column I > Ok
Result: we still have 320 in M28 and 28 in A28

4/ select only column I, menu Data > Sort > Column I > Ok
Result: we still have 320 in M28 and 28 in A28

The description says that sorting gives 25 in M28; in LO 4.1 and 4.0 this result is obtained with scenario 1/ only. Scenarios 2/ and 3/ give 100 in M28 and 4/ gives 305 in M28.
So the current version does not give the expected result and the bug, if there is a bug, is not fixed.

To be able to conclude we need a clear and unambiguous description of the scenario to be reproduced, the result expected and _why_ sorting this way should give this result.

Note: it should be more useful to have bug description with files from the real life instead of file without real meaning. In other words, sorting data randomly dispersed on a sheet is useless unless you can explain the corresponding problematic.

Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding.

Best regards. JBF
Comment 42 Luke 2014-10-28 02:21:11 UTC
Created attachment 108542 [details]
Comparision of Calc 4.4 vs Excel with Decending sorting A1:M500

This feature enhancement should have never been approved. The test case spreadsheet not only never worked on any version of Calc, but it also fails to work on Excel and Google Sheets. 

We have broken well established spreadsheet workflow for a contrived test case that never worked on any spreadsheet software.
Comment 43 Luke 2014-10-28 04:04:09 UTC
Created attachment 108549 [details]
RainersSample comparison 4.4 vs 4.2 vs Excel

Sorting RainersSample in 4.4 shows how implementing this "enhancement" breaks spreadsheets. Any older or Excel compatible spreadsheets designed like this will be broken in 4.4.
Comment 44 Joel Madero 2014-10-28 14:19:35 UTC
Removing myself from this bug - please don't readd me to the cc.
Comment 45 Eike Rathke 2014-11-26 18:57:54 UTC
Please redo comparisons of 4.4 against earlier releases or Excel with a build of today or later, especially regarding the UpdateReferenceOnSort=false case. Anything else is moot.
Comment 46 Eike Rathke 2014-12-04 21:04:49 UTC
This specific request is fixed as of 4.4 when activating Tools->Options->Calc->General "Update references when sorting range of cells" before sort. Note that this will render sorting behavior incompatible with all other spreadsheet applications and previous LibreOffice versions.