Bug 81089

Summary:	Sorting a spreadsheet causes formulas to change
Product:	LibreOffice	Reporter:	helplibreoffice
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED INVALID
Severity:	critical	CC:	ign_christian, jbfaure
Priority:	medium
Version:	4.2.5.2 release
Hardware:	Other
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:

Description helplibreoffice 2014-07-09 07:53:58 UTC

When sorting a spreadsheet, a user will expect formulas to change appropriately.  Unfortunately, when I sort a spreadsheet, the cell references in formulas sometimes change resulting in erroneous data or invalid references.  This makes sorting unusable and unreliable.

Comment 1 ign_christian 2014-07-09 08:34:29 UTC

Hello.. 

It's really like a duplicate to one of the bugs:
Bug 77018 -> external reference
Bug 79441 -> internal reference, different sheet
Bug 45146 -> internal reference, same sheet

Please mark this so if you find same behavior to one of those. If you think different, please provide sample file & procedure to see the problem.

Comment 2 pherworth 2014-09-12 22:11:33 UTC

Have sheet as follows:
Column A to E are in sort range and include
Col A Date
Col B description
Col C Description
Col D Debit Amount
Col E Credit Amount

Column F is not included in the range and has a formula on the lines "F1+E2-D2" to give a running balance.

After sorting A to E the col refs in the formula remain the same but the row refs change randomly (or so it seems).

The file is my bank details so I can't enclose it. If you really need it I can spend some time anonamising a copy, but I'd rather not.

Create say 10 rows as above and sort some of the rows - yoou'll see what I mean.

I'm trying LO 4.3.2.1 at moment. I first noticed this with the first 4.3.

Comment 3 Jean-Baptiste Faure 2014-09-13 07:30:08 UTC

I think this is a duplicate of bug 81633.
@pherworth: please could you try with attachment 104016 [details] ?

Best regards. JBF

Comment 4 pherworth 2014-09-14 22:01:45 UTC

This is exactly what mine does. Although the first 3 columns are sorted the formula columns shouls stay where they are.
Sorting your attachment (and my Spreadsheet) means all the intermediate totals are wrong unless you manually copy/drag the formula down the column(s) again.

Comment 5 pherworth 2014-09-14 22:13:49 UTC

I've read the comments on the other thread (bug 81633).
Surely if someone wants the formulas to move with the entries they can include those columns in the sort range.
If they're not in the sort range then they should stay. In any event I can't see any logic in the way they move.
And as I do most of my routine changes with macros it is particularly annoying to have to then make manual changes.
I hope we don't have to wait till next year!

Comment 6 ign_christian 2014-09-15 05:34:15 UTC

(In reply to comment #3)
> I think this is a duplicate of bug 81633.
Looks like pherworth reporting different issue than reporter. Since reporter experienced sorting problem in LO 4.2.5.2. But bug 81633 introduced since 4.2.7 & 4.3.1

Comment 7 pete_herworth 2014-09-20 11:02:10 UTC

I've just updated LO 4.3.2.1 to 4.3.2.2 and the sorting problem is still there. Does that mean it's still next year before it's fixed?

Comment 8 pherworth 2014-09-22 15:24:55 UTC

That last entry (comment 7) was me. I didn't realise I had 2 logins. Do we need to cancel one?

Comment 9 Jean-Baptiste Faure 2014-10-11 19:27:28 UTC

A bug report with no more precision than "sometimes" is useless. So closing as INVALID.

Please, feel free to reopen as UNCONFIRMED if you can provide a clear step by step description of the problematic behavior and attach a test file to reproduce it. Thank you for your understanding.

Best regards. JBF