Set version number to unspecified because it affects development versions of each branch 4.2 and 4.3 and probably the master.
Version: 4.2.7.0.0+ Build ID: f5949d09321e3ac62538df0e70e58284bd1cab32
Version: 4.3.1.0.0+ Build ID: f6445efb0e5c3de099f0425825fe1e6e8271dfb9

Best regards. JBF

After discussion on IRC, master is a better version number here.

Best regards. JBF

Tested to be sure: reverting "fdo#81309: Adjust references during sort." http://cgit.freedesktop.org/libreoffice/core/commit/?h=libreoffice-4-3&id=5c3bcc6fe3653aed79a11abbead66f11896e7ee8 on version 4.3.1.0.0+ (build ID: f6445efb0e5c3de099f0425825fe1e6e8271dfb9) under Ubuntu 14.04 x86-64 solves the problem for me.

Best regards. JBF

Confirmed
Ubuntu 14.04 x64
LibreOffice 4.4 master
Built: Mon Jul 21 13:38:10 2014 -0400

You guys need to talk to those users who think sorting should adjust references.

Calling this a regression is a testament to the meme that one man's feature is another man's bug.  We all lose.

Our best option would be to make it configurable.  Any attempt to automatically figure out when to and not to adjust would only make the situation worse, because unless we add a Google-level clever AI to do the guessing, we would never get it right, and there would always be some users with extreme corner cases coming out of the woodwork shouting "you broke my workflow!".

@Kohei - whatever you think is best. I'm sure this is annoying for you having different users expecting different results :( If you want to mark as WONTFIX or as an enhancement as you described - whatever is best opinion.

Marking as bibisected since the commit has been identified.

Created attachment 104016 [details]
another example of inconsistency in sorting

This attachment is the same as the previous except I have added a column with a formula using the OFFSET() function. This formula in column E gives the same result as the formula in column D. It is an attempt to keep the reference to the cell just above the current one.

If you select the range A2:E14 and sort ascending, you get several errors 523 in column E. Clearly it is not what is expected. If you do the same thing in LO 4.1.6 both columns still give the same result.

What is more inconsistent is that the sorting does not treat the part (Cn-Bn) in both columns the same way. It is updated in column D while it is not in column E.

As the change introduced by the fix for bug 81309 is big, I think it shouldn't be done in bugfix versions like 4.2.7 and 4.3.1. In other words, it is not a fix for a bug, it is a paradigmatic change.

Best regards. JBF

*** Bug 83391 has been marked as a duplicate of this bug. ***

*** Bug 83276 has been marked as a duplicate of this bug. ***

I think adjust it's fine, but in my understanding, sometimes wrong, it must be in the same way like Copy/Paste, adjust relative references and retain absolute references, like it is supposed a spreadsheet works. It's an user matter use properly relative/absolute references.

Now with sample in https://bugs.freedesktop.org/show_bug.cgi?id=83276:

A9: =IF(C9<C8;RANK(C9;$C$2:$C$16;0);IF(C9>C8;"Re-sort";IF(AND(C9=C8;B9<B8);"Re-sort";"")))

changing C9 value to 500,
after Menu/Data/Sort - Column C descending / Options - Range contain column labels. 
now in
A5: =IF(C5<C9;RANK(C5;$C$2:$C$16;0);IF(C5>C9;"Re-sort";IF(AND(C5=C9;B5<B9);"Re-sort";"")))

seems that references to the same row are updated properly (C5), but references to other rows change in a strange way for me (C8->C9), maybe they have several changes while sorting.

Forgive me, but I can't see why keep relative references changed in a different way than Copy/Paste, when a priori you don't know what data finish in what row.
I think most times comparisons/sums are with previous/next row, like in the sample, or to sum all the previous or all the rest rows/columns.

(In reply to comment #13)
> A9:
> =IF(C9<C8;RANK(C9;$C$2:$C$16;0);IF(C9>C8;"Re-sort";IF(AND(C9=C8;B9<B8);"Re-
> sort";"")))
> 
> changing C9 value to 500,
> after Menu/Data/Sort - Column C descending / Options - Range contain column
> labels. 
> now in
> A5:
> =IF(C5<C9;RANK(C5;$C$2:$C$16;0);IF(C5>C9;"Re-sort";IF(AND(C5=C9;B5<B9);"Re-
> sort";"")))
> 
> seems that references to the same row are updated properly (C5), but
> references to other rows change in a strange way for me (C8->C9), maybe they
> have several changes while sorting.

After sorting, the formula in A6 reads:
=IF(C6<C4,RANK(C6,$C$2:$C$16,0),IF(C6>C4,"Re-sort",IF(AND(C6=C4,B6<B4),"Re-sort","")))

Similarly, A10 reads:
=IF(C10<C5,RANK(C10,$C$2:$C$16,0),IF(C10>C5,"Re-sort",IF(AND(C10=C5,B10<B5),"Re-sort","")))

I agree that the references need updating properly - the current method updates some, but not all, relative references.

As far as I can tell, I now have no method of knowing reliably when to sort my tables - unless I copy a correct formula to all the rows after sorting my tables (not practical).

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8c18ffbf9bb250fd49c5fc7ae89cc74155c768c6

fdo#81633: Add a new configuration option to toggle ref update on sort.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.

Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=34580851065fc04af25e9c83a1106d1478f159e0

fdo#81633: Write test for this.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.

This one is now configurable.

Just FYI this will NOT be backported. So it'll be available in 4.4 daily in the next 24 hours.

(In reply to comment #18)
> Just FYI this will NOT be backported. So it'll be available in 4.4 daily in
> the next 24 hours.

Forgive my ignorance but does that mean I will have to wait until 4.4 is published in early 2015?

why can the fix not be part of 4.3.3

Do not reopen bugs that have been closed by a developer as FIXED.

I've talked to Kohei and he has reasons why it was not pushed to 4.3. If you want to look at the patch, review the patch, recommit the patch against 4.3, convince another developer to review that, etc. . . etc . . . you are free to do so. Else, yes, you will have to wait until 4.4. Removing myself from CC on this.

One other thing - you are free to use 4.4 daily builds (not recommended but you are free to do so)

http://dev-builds.libreoffice.org/daily/

*** Bug 83652 has been marked as a duplicate of this bug. ***

*** Bug 84022 has been marked as a duplicate of this bug. ***

*** Bug 84157 has been marked as a duplicate of this bug. ***

(In reply to comment #19)
> Forgive my ignorance but does that mean I will have to wait until 4.4 is
> published in early 2015?
> 
> why can the fix not be part of 4.3.3

Because there is an UI and string freeze in effect for release branches; see https://wiki.documentfoundation.org/ReleasePlan. You’re going to wait less than five months, not a big deal.

AJ wrote
>You’re going to wait less than five months, not a big deal.

Some might say that's a rather cavalier attitude, perhaps?
The way I see it is that some one (or people) broke LO some releases back for a  reason I don't understand.

Maybe 5 months is not a big deal to you, AJ, but I have to have LO working NOW the way it was - otherwise LO is as good as useless to me.  :-(

I simply cannot mess around with daily builds for five months or retro-installing a previous version.

Fortunately there are alternatives that DO work as LO used to. Yep - us users are fickle, all right. :-D

Ian.

I support Ian's comments.

I also wonder how many LO users have been sorting there data and not noticed the formulas have been corrupted.

*** Bug 84390 has been marked as a duplicate of this bug. ***

*** Bug 68566 has been marked as a duplicate of this bug. ***

Hmmmmmm .... This bug has become an epidemic it seems and, like Ebola, it needs swift action NOW, seeing it was LO developers that caused it in the first place.

I've been in electronics engineering all my life and using spreadsheets since Lotus 123 came on the scene.  Replacing something so basic (correct sorting) with something so esoteric I can't understand is sheer madness and totally reprehensible software development on the part of whoever introduced this new 'feature'.  This 'new feature' is what should have been delayed and ADDED to 4.4.  I can only hope LO software engineers have taken note and learned the lesson of not REPLACING long-established functions.  Geez ... I hope Microsoft NEVER does away with the Vulcan Nerve Pinch (AKA Ctrl-Alt-Delete) :-D
 
At risk of being banned from this thread, I'm going to recommend that anyone who MUST have their Calc spreadsheets working properly have a look at Apache Open Office.org - the latest version works properly for me ... and my relieved customers.

STATUS: RESOLVED FIXED?  I don't think so.  Nothing has been resolved or fixed for us users.

*** Bug 84754 has been marked as a duplicate of this bug. ***

I reported a duplicate of this bug. So I should be done. BUT, I have to really add my opinion here.

Ian is totally right. I am pissed off too. U can't do such a thing without a loud warning and not in a minor version.

I by myself was waiting that sort does not crash anymore or mixes up conditional formatting, not to speak off some printing stuff to be fixed. That were things I could see.

Now I thought all is working and U come up with some new hip functions which leads to silent corruption in tables I use for over 10 years!

U are so proud of Ur fast development cycle, think it over! Basic functions are just not working or have to handled with ugly workarounds, which never should have made it into an rc.

I am back at 4.1.6 I think. I don't know now, I have to check the other bugs which kept me from using the new versions.

I should create a bugs vs. versions matrix as a little helper to choose the right LO version for me, but I think I take sheet of paper in case I wanna sort this...

I asked the ESC to revert this commit from branches 4.2 and 4.3

https://bugs.freedesktop.org/show_bug.cgi?id=68566#c20
https://bugs.freedesktop.org/show_bug.cgi?id=68566#c22

Best regards. JBF

Just a thought ... 
Why not just replace the Calc program from 4.1.? (whatever the version that worked) into the next release and hold this "new feature" until 4.4?
Then those that absolutely must have this 'new feature' can keep on using the current version and those that want it to be the way it was can get on with the job of being productive again.

This 'new feature' has cost me a hell of a lot of time - not only working out what the problem was in the first place, but then having to placate hostile customers who blamed ME for stuffing up their spreadsheets.

I can see that no amount of strongly worded comments is going to get through the arrogance of developers, so I'm simply going to revert to, and stay with, Apache Open Office.

It is pointless staying with this bug report, so I'm removing myself from the CC list.

Good luck to all you others who want to wait for LO 4.4

Regards,
Ian.

Another way to avoid this new behavior if you are really annoyed is to switch back to LibO 4.1.6
http://downloadarchive.documentfoundation.org/libreoffice/old/4.1.6.2/

Kohei,
In 4.4 please change the default of Tools->Options->Calc->General->"Update Refs when Sorting" to UNCHECKED. It should be unchecked because the current behavior could be destructive to our users tables. This is also how all of the other major spreadsheets behave. MS Excel, AOO Calc, Google Sheets, Gnumeric, and Kingsoft Spreadsheets all do NOT update refs when sorting. Please change the default to follow the well established standard.

Currently the configuration option in master (I guess it is menu Tools > Options > LibreOffice Calc > Update reference when sorting range of cells) does not solve the problem.

If you try to sort the test file (attachment 104016 [details]) whatever the choice you do (check or uncheck this option) the result is false:
1/ option checked: result false in column D and correct in column E
2/ option uncheck: error 523 (Calculation does not converge) in row 3 to 6 in both columns D and E.

NB1: a quick way to verify if the sort is correct is to check the last value in columns D and C; it must be 784.79 whatever is the order of the rows.

NB2: to sort the data I do that: click in A2 then select rows 2 to 14 (maintain Shift key and click on the row headers 2 and 14).

Best regards. JBF

Created attachment 107586 [details]
Screenshot showing how sorting is still broken even after the patch

Confirmed. The table is not sorting correctly with either option.

Created attachment 107601 [details]
Both Kingsoft and Gnumeric do NOT automatically adjust references when sorting

(In reply to Jean-Baptiste Faure from comment #37)
> Currently the configuration option in master (I guess it is menu Tools >
> Options > LibreOffice Calc > Update reference when sorting range of cells)
> does not solve the problem.

I am pretty sure that I tested the fix right after it was pushed to the master and that it worked. I do not know what happened since this time. What is clear is that, now, it does not solve the regression. 

Best regards. JBF

I spent the better part of a day trying to figure out why my spreadsheets were not sorting properly. I've been using the same spreadsheets for the past 3 years. All of my values in this particular sheet were by reference to sheets, Points, Ringers and Master and used the usual $Points.A1 to $Points.V38 and the same for $Ringers and $Master.  Basically, the same problem mentioned by many others, i.e., unusual results. Doing an UNDO left behind REF errors. 

I was able to resolve the problem for this particular spreadsheet by doing a find and replace for every reference to read...$Points.$A$1, or, $Ringers.$X$10, etc. Somewhere I spotted this syntax and it got me through this full day problem. I recently upgraded to 4.3.1.2.  It seems that a known problem should be fixed when found, not wait for a future upgrade.

A fix has already been released for this bug, so changing status from 'NEW' back to 'REOPENED'.

*** Bug 84847 has been marked as a duplicate of this bug. ***

If someone is interested in a workaround, see my answer in:
http://ask.libreoffice.org/en/question/40686/calc-sort-messing-up-formulas/?answer=40912#post-id-40912

IMMHO, while we continue without a minimal previous QA for every new enhancement, not easy to find this kind of issues before release.

I don't know the effort it takes to implement such a minimal QA.

But if it is not in reach, just don't add new enhancements in between minor version updates and definitely not between 4.2.6 and 4.2.7  - just fix bugs.

LibreOffice has matured and it has a strong user base. Bug hunting becomes now really important. It is definitely more important for me as a user then the next newest feature.

U loose more users by annoying bugs then winning new ones with a new feature.

I expect troubles for the X.X.0 Version but after then it must become better.

A COMPACT EXAMPLE OF THE BUG: ===================

Here is a compact example of the bug. 
This is now fixed in the 4.3.4.0.0 2014-10-09 development version. (Thank you)

Note, this is clearly a BUG and not a FEATURE as this bug would break
imported spreadsheets from other programs (Excel, Gnumeric, ...)
In particular, this broke all my course grading spreadsheets.

EXAMPLE: if I consider the following spreadsheet
(which is sorted on the 1st column):

-----------------------------------------------------------------
ann	fff	=E1+F1+G1	=SUM(E1:G1)	1	2	3
barney	eee	=E2+F2+G2	=SUM(E2:G2)	4	5	6
charlie	ddd	=E3+F3+G3	=SUM(E3:G3)	7	8	9
-----------------------------------------------------------------

If I now sort on the 2nd column {ddd,eee,fff} the result is WRONG:

-----------------------------------------------------------------
charlie	ddd	=E1+F1+G1	=SUM(E3:G3)	7	8	9
barney	eee	=E2+F2+G2	=SUM(E2:G2)	4	5	6
ann	fff	=E3+F3+G3	=SUM(E1:G1)	1	2	3
-----------------------------------------------------------------

Note the relative references are handled correctly when I use
"=E1+F1+G1" but not when they are inside the argument of the 
function: "=SUM(E3:G3)"

Created attachment 107658 [details]
Short example of the bug

This is a short (3 line) example of the bug.

(In reply to Kohei Yoshida from comment #7)
> Our best option would be to make it configurable.  Any attempt to
> automatically figure out when to and not to adjust would only make the
> situation worse, because unless we add a Google-level clever AI to do the
> guessing, we would never get it right, and there would always be some users
> with extreme corner cases coming out of the woodwork shouting "you broke my
> workflow!".

One thing that baffles me with the new sort is that it changes cells outside of the selected sort range.

If I wanted the sort to change the cells, for instance keep references to rows, I'd include those columns in the sort selection.

Would it help if the concept was that only cells in the sort selection should be changed? (It would at least help me a bunch and I think it would offer a workaround for those that expects cells to change all over the place).

You could also add a little checkbox in the sort dialog that said something like "Enable Hollistic Sorting" :D

Another clue (at least in my case) to when I don't want calculations to change with sort is when I have one or several columns of values and then one or several columns of formulas referencing the value-columns. And these formulas are identical part from the references that all uniformly address the same row as the formula cell or the same number of rows above/below the formula cell - I.e. uniformly referenced formulas

However a better option in this case is to insert an an empty column between the "data" cells and the "formula" cells and in that way keep sorting from selecting the formula cells when sorting, and as per above then keep sorting from changing the references in the formula cells.

/E

Created attachment 107667 [details]
Test Case for cross sheet sort.

As I started to implement the workaround. I saw another annoying side effect.

I have a simple table without formulas on sheet1 and a summery by specific criteria on sheet2. These criteria are computed on sheet3. I made this division to let my users sort sheet1 without the risk of damaging any formulas.

What happens now is that on sheet3 with references to sheet1 the formulas are changed and sorted also.

So in sorting a table on one sheet I changed the formulas on another sheet.

I created a 2-sheet test case for this. Just sort sheet1 and see the effects on sheet2.

Wow; this bug is unreadably long & awful and seems to be collecting different problems.

Anyhow - Eike just merged the back-port of the option, defaulting to what should be the old behavior to the -4-3 branch, and it should be in the next 4.3 release (but not the one that is currently in progress AFAICS that'd be too much review work).

Hi Michael,

Thank you very much for that. I saw the commit and I am currently trying to test my build. I do not understand how to check if the option is available in my installation. Where should I look ? In the install dir or in the user profile? Is it necessary to restart with a clean new profile to have this option working?

Best regards. JBF

Ok, I found the option in the Expert Configuration window. Now, version 4.3 works the same way as 4.4 does. But in the case of the first attachment to this bug report (attachment 104016 [details]) both are currently wrong. It seems that the backward compatibility is not complete or broken somewhere. See comment #37 and comment #40.

Best regards. JBF

I am closing this bug report, considering that the option UpdateReferenceOnSort solves the backward compatibility problem.
For the remaining bug on the operation of this option (Err:523), I have filed a dedicated bug report (bug 85215).

Best regards. JBF

Created attachment 108255 [details]
Another example of inconsistency in sorting (updated)

Here is a new version of attachment 104016 [details] in which I have removed useless validity list and restored missing headers of columns A, B and C.

Best regards. JBF

*** Bug 85479 has been marked as a duplicate of this bug. ***

*** Bug 85405 has been marked as a duplicate of this bug. ***

From comment #8 in bug 85215 :

It works without error if, instead of selecting only the cell A1 and going to menu Data > Sort, I select the range A1:C14 (that is only the data) and sort on column "Date". It works the same if I select A1, hit the shift key and click on C14, or if I select the columns A, B and C and use the menu Data > Sort.

For me it is usable but it is a big change in the workflow which I was used to. I am sure I am not alone in this case.

Best regards. JBF

Clearly the bug is still far from resolved and "For me it is usable but it is a big change in the workflow which I was used to" is unacceptable to me and, possibly, other users.

The sorting should work without users having to sort in specific ways.

There is, I believe, a strong case for removing the new functionality which has caused all these problems and for a considered approach to be taken the issue with it only being released when it works.  I am hoping the developers have learned a lesson about releasing - let us not beat around the bush - half-baked functionality.

The release of the new functionality which corrupted formulae by sorting was unacceptable.  The time it is taking to fix the bug is also unacceptable.

Jean-Baptiste,
Why have you closed this bug report? This serious issue has still not been resolved. Sorting now requires users to know workarounds and change settings that don't always work. This should remain open until sorting functionality has been restored to work in the same way as Excel, AOO, and LO 4.1 and earlier work.

(In reply to Luke from comment #59)
> Jean-Baptiste,
> Why have you closed this bug report? 

I explained in comment #53 why I closed this bug report.

> This serious issue has still not been
> resolved. Sorting now requires users to know workarounds and change settings
> that don't always work. This should remain open until sorting functionality
> has been restored to work in the same way as Excel, AOO, and LO 4.1 and
> earlier work.

I disagree, this bug report became unreadable. The issue in the original description is fixed by the option configuration. Ok, it is not perfect, that is why I submitted the problem to the ESC. The fact is that we have different needs for different situations. Sorting in some situations worked for years and some others did not work, for years too. The metabug  bug 85490 try to summarize how sorting should work in different typical situations. Please, feel free to contribute to make the situation and the needs clearer.

Thank you for your understanding.

Best regards. JBF

Jean-Baptiste Faure,
Could you please give an example of a VALID spreadsheet that "did not work for years too"? The one "synthetic" example in your Meta is completely broken in Excel,Google Sheets, and never worked in any spreadsheet until recently. The "fix" for that Bug 45146 introduced this serious regression.

Thank you very much for following up with this report and with the ESC, but I do not think we can consider this issue resolved until the old functionality is restored.

(In reply to Luke from comment #61)
> 
> Thank you very much for following up with this report and with the ESC, but
> I do not think we can consider this issue resolved until the old
> functionality is restored.

I agree with Luke.

Gentlemen,

As an outsider who has only begun reading about this issue (since the change in workflow affected me) I would like to add my perspective.

I think it is essential that default behaviour should not be changed in any mature application unless there is overwhelming evidence that the advantages outweigh the disadvantages.  Furthermore, I think it is important (though not always essential) to mirror the default behaviour of significant competitors, in this case Excel.

The change in sort behaviour is so significant and so counter-intuitive for those of us coming from other products, that I believe it should not be made unless the default behaviour can remain compatible.

Just my $0.02 worth.

-Nigel

*** Bug 85571 has been marked as a duplicate of this bug. ***

The consensus appears to be that this issue is still not resolved. I wasted hours and lost data by making the mistake of upgrading to 4.3.2.2, the version that still offered on the website.

By the guidelines at https://wiki.documentfoundation.org/QA/BugTriage
That qualifies this as a serious bug eligible for back ports.

Please, have a look at bug 85215 and bug 85584.

Setting back to resolved fixed.

Best regards. JBF

*** Bug 85571 has been marked as a duplicate of this bug. ***

*** Bug 85968 has been marked as a duplicate of this bug. ***

*** Bug 86170 has been marked as a duplicate of this bug. ***

*** Bug 83864 has been marked as a duplicate of this bug. ***

Making this change to LibreOffice 4.2.7.2 has broken many very old spreadsheets and caused them to be mangled in sorting.  There is no way to resolve this post with this one:

https://bugs.freedesktop.org/show_bug.cgi?id=81309

I am in that camp.  I want sorting to stay as it was, the sorting that keeps old sheets sorting exactly like Excel and 4.2.6 did.   

However, I hear you and I have a suggestion.   First lets revisit what we think sorting should be.  IMHO, sorting is equivalent to cutting and pasting entire rows of the sort range and by hand manually reordering them. Note, I did not say COPY and paste.  Copy and cut do different things to references depending on whether the reference in the sort is to a cell that is inside or outside the copy or cut region, and whether it is a A1, $A1, A$1, $A$1 reference. Further, for references that appear in cells outside the copy/cut range but (before the operation) refer to cells inside the range, there are again adjustments made.

For instance, when I cut and paste a range, A1 references to a location inside the range are changed, A1 references to a place outside the range are not.  If I copy and paste, both are changed, that is why we have $ to keep selected references from being changed in a copy and paste.        

The way copy/cut and insert columns/rows works makes spreadsheets work in a way that is common so spreadsheets with all forms of $ references (not code) can be moved from one program to another and work.  If you break that, you greatly change the outlook for LibreOffice.   

However, I hear these people.  I suggest a new character with a new action "!" so that !A!1 is not changed ever, not by copy, not by paste, not by sort.  The people who wanted this change can decide what they want the action to be of a reference to !Z!100 in A1 to be when inserting rows/columns at M50.  I will live with what they decide.  
   
I believe this will meet the desires of "bug" post.

*** Bug 86304 has been marked as a duplicate of this bug. ***

*** Bug 87106 has been marked as a duplicate of this bug. ***

*** Bug 87317 has been marked as a duplicate of this bug. ***

*** Bug 90122 has been marked as a duplicate of this bug. ***

*** Bug 85981 has been marked as a duplicate of this bug. ***

Migrating Whiteboard tags to Keywords: (bibisected)
[NinjaEdit]

Why in the world do I have to go to Tools -> Options -> Calc -> General and click a checkbox to make this work correctly? 

This should be a checkbox in the Options tab of the Sort dialog, not a system-wide preference.

7qia0tp02,
A closed bug is not the proper place to discuss UI. If you want to help out the UX team, you can discuss this issue on IRC or the UX mailing list. 
https://wiki.documentfoundation.org/Design

This is not a simple UI tweak. This setting was not placed in the Sorting dialog, because it’s application wide preference. In its current form it cannot be toggled on or off like all the other options in the Sort dialog.  

It’s a big job to make a feature easier to enable that breaks compatibility with old Calc spreadsheets and every other spreadsheet application. If you're a programmer and want to hack on it, there's lots of useful information on the wiki to help you get started on it.