Created attachment 53614 [details] Spreadsheet illustrating the issue of sorting by row Problem description: When you sort BY ROW a Spreadsheet containing reference to "one cell on the row just on the right" (for example B2 using a reference to A1) the resulting spreadsheet cells keep reference to the old reference (if A1 moved to C1 and B2 to D2, then D2 still reference A1 and not C1). Steps to reproduce: 1. Open "SupprisingSortResult.ods" attached 2. Select $Feuille2.a1:h3 3. Sort by row on the 3rd line (that is) Menu "Data", item "Sort..." "option" direction "from left to right (row sorting)" "sort criteria" "sort by" "line 3" "OK" Current behavior: 1st line is sorted (A B C D ) 2nd line isn't sorted (A.a B.b B.a A.b C.a D.b D.a C.b) 3rd line is sorted (A.1 A.2 B.1 B.2 C.1 C.2 D.1 D.2) Expected behavior: 1st line is sorted (A B C D ) 2nd line is sorted (A.a A.b B.a B.b C.a C.b D.a D.b) 3rd line is sorted (A.1 A.2 B.1 B.2 C.1 C.2 D.1 D.2) This is expected because before the sort A.b is in the same row as A.2 ; B.b as B.2... Platform (if different from the browser): Browser: Mozilla/5.0 (Windows NT 6.0; rv:8.0) Gecko/20100101 Firefox/8.0 Remark : This is NOT an issue when sorting by line (as more usual). You can use $Feuille1 to ensure that. In "a" previous version sorting by line has a similar problem, but it is not the case any more. I think that this "resolved" issue is the object of the Bug#36073. As I used an French localised version of LibreOffice, I have to guess the menu, item, button... label in English (I hope that I'm not to far from the real ones).
One possible workaround IN THE PRESENT example : If you replace "B2"=A1&".b" by "B2"=OFFSET(B1;0;-1)&".b", then the resulting sorted spreadsheet will behave itself. BUT this is not a general solution. It only works when the raw "just on the left" is the same before and after the sort. In other words, that workaround works when each pair of row (A and B, C and D, E and F...) are mean to be AND stay together.
It seems [Reproducible] with "LibreOffice 3.4.4 - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:402)]" I expect that after sort the references will be to the cell with the same contents as before Before sort: Cell above "B.1" references to A1="B" After Sort: Cell above "B.1" references to C1="B" ok Before sort: Cell above "B.2" references to A1="B" After Sort: Cell above "B.2" references to A1="A" WRONG Before sort: Cell above "A.1" references to C1="A" After Sort: Cell above "A.1" references to A1="A" ok Before sort: Cell above "A.2" references to C1="A" After Sort: Cell above "A.2" references to C1="B" WRONG Before sort: Cell above "D.1" references to E1="D" After Sort: Cell above "D.1" references to G1="D" ok Before sort: Cell above "D.2" references to E1="D" After Sort: Cell above "D.2" references to E1="C" WRONG It seems that all references below empty cells get wrong after sort, but I am not sure whether I understood the mechanism, it has not to do with "empty". And yes, works fine with sort by column. @Kohei: Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug. - Reported with Bug Submission Assistant -
Seems to be inherited from OOo, already a problem with OOo 3.1.1. Still a problem with Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) ENGLISH UI [(Build ID: d38713d-5d03837-ca7e6f5-c4bb9bd-ce71330)]" ( Voreppe_Win32_Tinderbox 111115)
Doesn't work with "LibreOffice 3.5.6.2" Version ID : e0fbe70-5879838-a0745b0-0cd1158-638b327
Sorry, I'd learn that "Version" is most old version of LO where bug appeared. Not current version. Changing back to 3.3.0 Beta2
Although this bug report is older than bug 45146, lots of other bug reports have been marked as duplicates of bug 45146, making bug 45146 more appropiate to act as the central report. *** This bug has been marked as a duplicate of bug 45146 ***
For the record: - does not work as expected in LO 4.0.6 and LO 4.1.6 - works as expected in LO 4.2.6.3 - works as expected in LO 4.3.4.0.0+ only if hidden configuration option "UpdateReferenceOnSort" is set to true (default value: false). If option is set to false the result of sorting is different from what it was in 4.0 and 4.1: A B C D B.a B.b A.a A.b D.a D.b C.a C.b A.1 A.2 B.1 B.2 C.1 C.2 D.1 D.2 that is no change in row 2. - same behavior with LO 4.4.0.0.alpha+ as with LO 4.3.4.0.0+ Build ID for 4.3.4.0.0+ : f9050536650835b776b9aa022ae1b47315cd5e14 Build ID for 4.4.0.0.alpha+: 68a18a6249dc9d8842a72413f3cd6f39ecf416e3 Best regards. JBF
The observation in comment 7 is actually bug 83765, fixed in master and pending review for 4-3 and 4-2