Created attachment 96861 [details] Zip file containing 2 sample documents where the error is clearly evident Problem description: Formula in cells referencing cells in another linked spreadsheet are incorrectly updated when these cells are sorted. The formulas are automatically changed to reference completely different and seemingly unrelated cells in that other spreadsheet. This bug was also noted when rows are moved or added, but the issue is demonstrated below when data is sorted. Steps to reproduce: 1. Download the attached zip file which contains 2 .ods spreadsheets and extract both files into 1 folder. 2. Open FS.ods and say "Yes" to update links. (Ensure links are updated and no error is given. The only spreadsheet that is linked is "Bookkeeping.ods") 3. Open tab "IS" (All references to ranges that follow are in reference to this tab only, unless indicated otherwise) 4. Take mental note of all formulas in the range B12:B24. You will notice they all reference some cell in row 403 of the "Bookkeeping.ods" spreadsheet. 5. Select range A12:AMJ24 (all rows in the "Operating Expenses" section) 6. Click Data >> Sort 7. Select "Sort key 1" to be Column A and all other sort keys as undefined (sorting by the first column) and click OK 8. You will now notice that values in range B12:B24 are all changed. On my machine they are all zero. You will also note that all references to row 403 are now references to various other rows, which is definitely incorrect behavior. Current behavior: See step 8 above. Expected behavior: Expected all references to remain the same as before the sort i.e. referencing some particular cell in Bookkeeping.ods but always row 403. The values should not change after the sort. Operating System: Windows 7 Version: 4.2.2.1 release
Hi. Reproduced (same LibO version, same OS) It looks like an attempt to maintain the moved cell taking into account the move during the sort. Eg: "M Advertizing", previously referencing AR403, moves from row 12 to row 24 after the sort and then references AR391 (391 = 403 - (24-12)). Wrong for 2 reasons : - the reference to cell "=file...AR403", even typed without the "$", have no relative position to cell in an other file, - it seemed to me agreed that cells moved by a "sort" have not their references updated (contrary to explicit moves). An other view on unclear situation after a move : - before the sort, fill C12 with "=C34", copy C12 to C13:C24, - look at the content of C12:C24 after the sort : inexplicably unchanged, despite it was in the selected area to be sorted. IMHO there are no more BUGS related to the sort feature, there is rather an OBSCUR and FLUCTUATING specification.
Reproduce with many versions: - LibO 3.3.0.4 till 4.3.0.beta1 - OOo 3.1.1, 3.3.0 - AOO 3.4.0 NOT reproduce with AOO 3.4.1, AOO 4.0.0, AOO 4.0.1, AOO 4.1.0 So the bug was fixed by AOO 3.4.1, but not in LibO project. Change version accordingly.
Created attachment 100089 [details] Zip containing 2 files linked. Open test.ods I propose a new simpler test file which compares behavior of sorting with absolute or relative references, with external/internal to other sheet/internal same sheet references. 1. Extract both files in the same directory 2. Open test.ods 3. Accept to update links 4. Select A15:D20 or A23:D28 (if you want to test absolute or relative references) 5. Sort descending according to column A Expected behavior: With relative external references or relative internal references to other sheet, formulas should be changed to reflect the new order. Current behavior: Formulas with relative external references are not updated. For relative internal to other sheet references, behavior changed between LibO 4.2.4.2 and LibO 4.3.0.0 beta1: with 4.2 and prior, formulas ARE updated; with 4.3 formulas are no more updated (but this may be another bug). No problem with absolute references. For relative internal references to the same sheet, formulas can be updated only if source is also sorted: move Feuille1.A1:A6 to Feuille1.E15:E20 and select A15:E20 before sorting. Then formulas are correctly updated.
As AOO solved this bug between AOO340 and AOO341, I tried to find the code modification with issue 110588 https://issues.apache.org/ooo/show_bug.cgi?id=110588 But AOO commit 55737a716866 does not concern this point: https://bitbucket.org/mst/ooo340/commits/55737a7168666bfee5f113ec2655b69d174de77e as it deals with issue 113183 https://issues.apache.org/ooo/show_bug.cgi?id=113183 In LibO commits, I found this commit concerning issue 110588 http://cgit.freedesktop.org/libreoffice/core/commit/?id=fbd12549299a2fccab00e3f5a9d0bac161170b75&context=12 When checking LibO recent code, it appears that this commit was reverted: http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/token.cxx#520 http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/token.cxx#2236 I tried to re-revert it, but it changed nothing on LibO 4.3 behavior in this case.
*** Bug 64647 has been marked as a duplicate of this bug. ***
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=db0a4c2575ffba1c170d74882b0f5a6229ec072f resolved fdo#77018 keep external references intact 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.
Pending review for 4-3 at https://gerrit.libreoffice.org/10243 for 4-3-0 at https://gerrit.libreoffice.org/10244 for 4-2 at https://gerrit.libreoffice.org/10245 for 4-2-6 at https://gerrit.libreoffice.org/10246
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7aa2b94ac57b7c99e1e45d5f652a423ee603dd55&h=libreoffice-4-3 resolved fdo#77018 keep external references intact 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=92216be6ce13990b8ea6b6264c656d2bc1746401&h=libreoffice-4-2 resolved fdo#77018 keep external references intact 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-3-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=37b97c83aa23d5a7c1687126d93cf1a69ce4a2d0&h=libreoffice-4-3-0 resolved fdo#77018 keep external references intact during sort It will be available already in LibreOffice 4.3.0. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-4-2-6": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9e83928e4dd65a1cf570f9cea65f456bd089ea33&h=libreoffice-4-2-6 resolved fdo#77018 keep external references intact during sort It will be available already in LibreOffice 4.2.6. 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.
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
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1d67f3661cdf1fadf498ae77f9d8ee9c2e026f31 unit test for sort with sheet references and external references, fdo#77018 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.