[Solved] Spreadsheet corruption caused by sorting

[Vlad_Inhaler]

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 C21-D21 - 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.

[acknak]

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

[Vlad_Inhaler]

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:

• 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 running-total 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.

I suppose if the worst comes to the worst, I might have to change all entries and/or figures and - assuming the problem remains - send in that sheet. That would be a last resort. I am busy this evening but will have another go at things tomorrow. Thanks.

[martin_XP]

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."

[acknak]

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.

[lesliebee]

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 A-Z" 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

[acknak]

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.