This is about OOo_3.1.0_Win32Intel, en_US running under WinXP.
Cols are A..L, 157 rows (to give an indication of size)
Column A is date,
B is some text,
D is net income,
E is tax due on D (say 15%),
C is D+E,
F, G and H are C, D and E but for expenditure
I and J are special (and irrelevant)
K is a running total.
E20 might be D20*0.15 and C20 might be D20+E20
D21 might be C21/1.15 and E21 might be C21D21  I use both forms as I need them.
 I insert a new row (row 75), copy another row (row 76) to that one and then change the 'date' field of row 75.
 now I sort on cols A..K on 'Date' (column A) and the text in column B.
Now that new row has been sorted correctly to row 40, but E40 is D75 x 0.15 and C40 is D75+E75
All such fields in the rows 41 to 74 are now pointing to the wrong row, E50 is D51 x 0.15
Luckily for me, that bug is so major that I noticed it immediately. Because I use both the forms as I need them I was getting circular references.
Reverting back to 3.0.1 was the only solution I could find. Once a spreadsheet has been sorted, it is so screwed that the only way out is to discard changes and start again.
[Solved] Spreadsheet corruption caused by sorting

 Posts: 3
 Joined: Tue May 12, 2009 7:31 pm
[Solved] Spreadsheet corruption caused by sorting
Last edited by Hagar Delest on Tue Feb 15, 2011 12:32 pm, edited 2 times in total.
Reason: tagged Solved.
Reason: tagged Solved.
OOo 3.1.X on openSuse 11 + MS Windows XP
Re: Spreadsheet corruption caused by sorting
Are you sure that all the formulas in each row refer only to that same row?
Do any of your formulas have "absolute" addresses (i.e. use dollar signs)?
Can you attach document with sample data and instructions for sorting it? You can use the "Upload Attachment" link (below the message entry area after you click "POST REPLY"). [Forum] How to attach a document here
Do any of your formulas have "absolute" addresses (i.e. use dollar signs)?
Can you attach document with sample data and instructions for sorting it? You can use the "Upload Attachment" link (below the message entry area after you click "POST REPLY"). [Forum] How to attach a document here
AOO4/LO5 • Linux • Fedora 23

 Posts: 3
 Joined: Tue May 12, 2009 7:31 pm
Re: Spreadsheet corruption caused by sorting
I have created a test sheet which recreates the feel of the spreadsheet but without the names/numbers. It works just fine and this means I am going to have to experiment some more.
Answering the questions:
Answering the questions:
 No, some of the cells refer to the cell above (E46 is E45). The primary sort key (Col A) will be the same for both, the secondary keys are such that the two lines will always be together and in the same order.
 Yes, but not in the sorted area. There is a runningtotal column which contains =SUM(C$2..C45)SUM(F$2..F45)
 I do not want to post my accounts. I am trying to devise a test spreadsheet which demonstrates the bug, so far without success.
OOo 3.1.X on openSuse 11 + MS Windows XP
Re: Spreadsheet corruption caused by sorting
A1 contains 324
A2 contains 343
A3 contains 6567
A4 contains 34
A5 contains 34
A6 contains 455
B1 contains =A1*0,15 (outcome 48,60)
B2 contains =A2*0,15 (outcome 51,45)
B3 contains =A3*0,15 (outcome 985,05)
B4 contains =A4*0,15 (outcome 5,1)
B5 contains =A5*0,15 (outcome 5,1)
B6 contains =A6*0,15 (outcome68,25)
C1 contains empty
C2 contains empty
C3contains = B3+A3 (outcome 7552,05)
C4 contains = B4+A4 (outcome3 9,1)
C5 contains = B5+a5 (outcome 39,1)
C6 contains empty
after sorting in OO 3.0.1 (m15 (build:29379) ) Polish version from ux.pl
sorted area A1:C6
sort by A column
A1 34
A2 34
A3 324
A4 343
A5 455
A6 6567
B1 =A1*0,15 (5,1)
B2 =A2*0,15 (5,1)
B3 =A3*0,15 (48,6)
B4 =A4*0,15 (51,45)
B5 =A5*0,15 (68,25)
B6 =A6*0,15 (985,05)
ALL is OK
C1 = B1+A1 (39,1)
C2 = B2+A2 (39,1)
C3 empty
C4 empty
C5 empty
C6 = B6+A6 (7552,05)
ALL is OK
after sorting in OO 3.1.0 ( m11 (Build:9399)) Polish version
sorted area A1:C6
sort by A column
A1 34
A2 34
A3 324
A4 343
A5 455
A6 6567
B1 =A1*0,15 (5,1)
B2 =A2*0,15 (5,1)
B3 =A3*0,15 (48,6)
B4 =A4*0,15 (51,45)
B5 =A5*0,15 (68,25)
B6 =A6*0,15 (985,05)
ALL is OK
C1 = B4+A4 (394,45)
C2 = B5+A5 (523,25)
C3 empty
C4 empty
C5 empty
C6 = B3+A3 (372,60)
ERROR acting like "absolute" addresses when column not full of formulas or formulas different
i don't have luck attaching file : "Sorry, the board attachment quota has been reached."
A2 contains 343
A3 contains 6567
A4 contains 34
A5 contains 34
A6 contains 455
B1 contains =A1*0,15 (outcome 48,60)
B2 contains =A2*0,15 (outcome 51,45)
B3 contains =A3*0,15 (outcome 985,05)
B4 contains =A4*0,15 (outcome 5,1)
B5 contains =A5*0,15 (outcome 5,1)
B6 contains =A6*0,15 (outcome68,25)
C1 contains empty
C2 contains empty
C3contains = B3+A3 (outcome 7552,05)
C4 contains = B4+A4 (outcome3 9,1)
C5 contains = B5+a5 (outcome 39,1)
C6 contains empty
after sorting in OO 3.0.1 (m15 (build:29379) ) Polish version from ux.pl
sorted area A1:C6
sort by A column
A1 34
A2 34
A3 324
A4 343
A5 455
A6 6567
B1 =A1*0,15 (5,1)
B2 =A2*0,15 (5,1)
B3 =A3*0,15 (48,6)
B4 =A4*0,15 (51,45)
B5 =A5*0,15 (68,25)
B6 =A6*0,15 (985,05)
ALL is OK
C1 = B1+A1 (39,1)
C2 = B2+A2 (39,1)
C3 empty
C4 empty
C5 empty
C6 = B6+A6 (7552,05)
ALL is OK
after sorting in OO 3.1.0 ( m11 (Build:9399)) Polish version
sorted area A1:C6
sort by A column
A1 34
A2 34
A3 324
A4 343
A5 455
A6 6567
B1 =A1*0,15 (5,1)
B2 =A2*0,15 (5,1)
B3 =A3*0,15 (48,6)
B4 =A4*0,15 (51,45)
B5 =A5*0,15 (68,25)
B6 =A6*0,15 (985,05)
ALL is OK
C1 = B4+A4 (394,45)
C2 = B5+A5 (523,25)
C3 empty
C4 empty
C5 empty
C6 = B3+A3 (372,60)
ERROR acting like "absolute" addresses when column not full of formulas or formulas different
i don't have luck attaching file : "Sorry, the board attachment quota has been reached."
OOo 3.0.X on Ms Windows XP
Re: Spreadsheet corruption caused by sorting
Yes, I see it also.
It looks like this is the source of the problems with sorting in 3.1.
Ok, here's the issue: Issue 101690: Sort in CALC mixes up references to cell identifiers.
It's already fixed and scheduled to be released in 3.1.1, but I've not seen a date for that yet... Yikes! They're talking about late August for 3.1.1. That seems like way too long for fixes to serious problems like this.
It looks like this is the source of the problems with sorting in 3.1.
Ok, here's the issue: Issue 101690: Sort in CALC mixes up references to cell identifiers.
It's already fixed and scheduled to be released in 3.1.1, but I've not seen a date for that yet... Yikes! They're talking about late August for 3.1.1. That seems like way too long for fixes to serious problems like this.
AOO4/LO5 • Linux • Fedora 23
Re: [Issue] Spreadsheet corruption caused by sorting
I am not an IT expert so am not sure if the above issue is the same one I am experiencing. Here is a description of a bug I have found, which I think may be a simpler example of what is being described above.However, my explanation may contain extra information to help track down the bug.
In Calc 3.1.0 on MacOS 10.5.7 (Leopard, up to date)
Cells in a row contain formulae that refer to other cells in that SAME row.
All works fine. Spreadsheet updates correctly and all formulae work fine.
If I now select (with mouse, using the row headers on the left) a contiguous number of rows, and then click with the mouse the "Sort AZ" button in the toolbar at the top of the screen, the spreadsheet appears to update, BUT THE FORMULAE ARE NOW WRONG. They seem to be behaving as absolute, not relative, references, so that they now point to cells in THE ORIGINAL rows. The columns references are retained.
This problem is *NOT* present in NeoOffice 3.0 Patch 4. It is only present in OpenOffice 3.1.0
In Calc 3.1.0 on MacOS 10.5.7 (Leopard, up to date)
Cells in a row contain formulae that refer to other cells in that SAME row.
All works fine. Spreadsheet updates correctly and all formulae work fine.
If I now select (with mouse, using the row headers on the left) a contiguous number of rows, and then click with the mouse the "Sort AZ" button in the toolbar at the top of the screen, the spreadsheet appears to update, BUT THE FORMULAE ARE NOW WRONG. They seem to be behaving as absolute, not relative, references, so that they now point to cells in THE ORIGINAL rows. The columns references are retained.
This problem is *NOT* present in NeoOffice 3.0 Patch 4. It is only present in OpenOffice 3.1.0
OOo 3.1.X on Mac OSx Leopard + Linux
Re: [Issue] Spreadsheet corruption caused by sorting
Thanks for your input, but this is not the place to communicate with the programmers working on OOo. You can do that through the issue in the tracker that was linked to above. [Tutorial] Reporting bugs or suggestions
What you describe definitely sounds like the same bug, which is already fixed. If you're interested in verifying and testing the fix, you'll have to get one of the snapshot builds that includes the fix and test the sorting operation with your sheet.
What you describe definitely sounds like the same bug, which is already fixed. If you're interested in verifying and testing the fix, you'll have to get one of the snapshot builds that includes the fix and test the sorting operation with your sheet.
AOO4/LO5 • Linux • Fedora 23