When sorting, Calc should automatically adjust cell references such that formula cells containing cell references not change their values after the sort. Bug 45146 is probably a similar case.
I'm working on this.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5c6ee09126631342939ae8766fe36083d8c011e3 fdo#81309: Adjust references during 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=68ae3a08369518e2ddd8340f27402d9b0d3df734 fdo#81309: 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.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a06980f6d0cdc245345bd38440e54d850a058f4b fdo#81309: Writer another test. 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=5ed9dea7fa3df2bebcda116734dc8643eb35be18 fdo#81309: Write another test. 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=9943473a4c4e5dd464d8bcb44e1f477f5045c22f fdo#81309: Write test for out-of-place sorting. 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.
Now it's on master.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=5c3bcc6fe3653aed79a11abbead66f11896e7ee8&h=libreoffice-4-3 fdo#81309: Adjust references during sort. It will be available in LibreOffice 4.3.1. 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.
*** Bug 65158 has been marked as a duplicate of this bug. ***
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4aed409279d3b9b8b95d84418fb7f279367cc30&h=libreoffice-4-2 fdo#81309: Adjust references during sort. It will be available in LibreOffice 4.2.7. 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.
Hi Kohei, (In reply to comment #0) > When sorting, Calc should automatically adjust cell references such that > formula cells containing cell references not change their values after the > sort. Are you sure it should always be the case? If you make a sort on the file https://bugs.freedesktop.org/attachment.cgi?id=103205 you get a false result with 4.3.1.0.0+ when it is correct with all previous versions. Precisely: 1/ open the file, it is a simplified extract of bank account. Column D gives the balance between incomes (column C) and spendings (column B). Each formula in column D refers to the value in the previous row in the same column. 2/ select rows 8 to 14 3/ click on the button "Sort ascending" on the dates (column A) The formulas in D8 and D14 are false Best regards. JBF
JBF, please file a new bug.
(In reply to comment #12) > JBF, please file a new bug. Done : bug 81633 Best regards. JBF
Hi Kohei, (In reply to Commit Notification from comment #10) > Kohei Yoshida committed a patch related to this issue. > It has been pushed to "libreoffice-4-2": > [...] > It will be available in LibreOffice 4.2.7. It is a surprising idea to backport a patch implementing an enhancement (AFAIK it is you who set this report as enhancement) to the last release of the 4.2 branch. I do not know if it is possible to revert this commit for 4.2.7. I tried to revert the commit for 4.3 and git said it was not possible. Will try for 4.2.7. Best regards. JBF
*** Bug 72487 has been marked as a duplicate of this bug. ***
*** Bug 55964 has been marked as a duplicate of this bug. ***
*** Bug 46641 has been marked as a duplicate of this bug. ***
Jean-Baptiste Faure, According to: http://support2.microsoft.com/kb/40401 To correctly sort a range that includes cells with references, all references must be absolute references. In every duplicate report, people are incorrectly using relative references. Clearly this is not a bug Calc. These are bug reports that should have been all closed as INVALID.
(In reply to Luke from comment #18) > Jean-Baptiste Faure, > According to: > http://support2.microsoft.com/kb/40401 > To correctly sort a range that includes cells with references, all > references must be absolute references. Microsoft is not a scientific reference. According to the leap year bug (https://en.wikipedia.org/wiki/Leap_year_bug) I can't trust Microsoft when it say that something must be done that way. > > In every duplicate report, people are incorrectly using relative references. > Clearly this is not a bug Calc. These are bug reports that should have been > all closed as INVALID. Please note that this bug report is an enhancement. Best regards. JBF
Making the change suggested in: https://bugs.freedesktop.org/show_bug.cgi?id=81633 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 that one. I am in this camp. I want the sorting of references 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, so one can make new rows in table-like calculations that use some common constants at the top in every row. 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 those 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 solution to the bank statement problem with the running balance down the side is to use !A!1 type reference in the running balance column that refer to the data in the range that might be sorted. Then, when sorting, do not include the running balance column in the sort range. The people who want something different in the way references are handled 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. Further, I will let them decide what happens with a drag-extend pseudo-copy-paste on these cells. I suggest the references changing would be most convenient, but I will let them decide. I will live with what they decide because as present ! does not appear in any of my old sheets. If I find it useful, I will use it. If other people find it useful, it may find its way into other spreadsheet programs. I believe this will meet the desires of that "bug" post without breaking what people are relying on in this post.
For the records, 4.2.8 and 4.3.5 will restore the old behavior, for 4.4 the UpdateReferenceOnSort option is defaulted to false/unchecked.