Bug 1118983 - Sorting columns with formulae referencing cells in other sheets broken
Summary: Sorting columns with formulae referencing cells in other sheets broken
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Fedora
Classification: Fedora
Component: libreoffice
Version: 20
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Eike Rathke
QA Contact: Fedora Extras Quality Assurance
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-07-12 17:17 UTC by Anil Seth
Modified: 2014-07-19 06:03 UTC (History)
6 users (show)

Fixed In Version: libreoffice-4.2.5.2-6.fc20
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2014-07-19 06:03:40 UTC


Attachments (Terms of Use)
Sorting the 3 rows on the first sheet demonstrates the problem (11.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-12 17:17 UTC, Anil Seth
no flags Details
Sorting the 3 rows on the first sheet demonstrates the problem (13.17 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-18 09:32 UTC, Anil Seth
no flags Details


Links
System ID Priority Status Summary Last Updated
FreeDesktop.org 79441 None None None Never

Description Anil Seth 2014-07-12 17:17:08 UTC
Created attachment 917544 [details]
Sorting the 3 rows on the first sheet demonstrates the problem

Description of problem:
A sheet references data from other sheets. If the data is sorted, the result is incorrect. E.g.
Sheet 1 A & B columns
c  =sheet2.b3 (value 3)
a  =sheet2.b1 (value 1)
b  =sheet2.b2 (value 2)

& Sheet2 A & B columns
a 1
b 2
c 3

Sorting the first sheet on first column results in incorrect formulae.

Version-Release number of selected component (if applicable):
4.2.5.2-1.fc20

How reproducible:
Every time

Steps to Reproduce:
1.Create a document with 2 sheets as above
2.Sort the first sheet on first column (ascending)
3.

Actual results:
a #Ref
b 1
c 0

Expected results:
a 1
b 2
c 3

Additional info:
attaching the trivial sheet to reproduce the problem

I suspect that it may be a regression because I recall using similar functionality less than a year ago.

Comment 1 Fedora Update System 2014-07-16 11:55:46 UTC
libreoffice-4.2.5.2-6.fc20 has been submitted as an update for Fedora 20.
https://admin.fedoraproject.org/updates/libreoffice-4.2.5.2-6.fc20

Comment 2 Fedora Update System 2014-07-17 04:29:56 UTC
Package libreoffice-4.2.5.2-6.fc20:
* should fix your issue,
* was pushed to the Fedora 20 testing repository,
* should be available at your local mirror within two days.
Update it with:
# su -c 'yum update --enablerepo=updates-testing libreoffice-4.2.5.2-6.fc20'
as soon as you are able to.
Please go to the following url:
https://admin.fedoraproject.org/updates/FEDORA-2014-8398/libreoffice-4.2.5.2-6.fc20
then log in and leave karma (feedback).

Comment 3 Anil Seth 2014-07-17 17:18:04 UTC
Thanks. The above version solved the problem. However, I noticed one related issue.

Suppose you have a cell outside the sort range which references a cell in the sort range. The cell outside the range continues to refer to the old position.

E.g. In the sample example, on Sheet1, 
C5 has the formula =A2 (value a).

After sorting the first three rows, 
C5 still =A2 (value b).

Comment 4 Eike Rathke 2014-07-18 08:55:15 UTC
(In reply to Anil Seth from comment #3)
> E.g. In the sample example, on Sheet1, 
> C5 has the formula =A2 (value a).

Which sample are you talking about? Neither the here attached document nor the one of the referred FreeDesktop bug has a formula in C5.

> After sorting the first three rows, 
> C5 still =A2 (value b).

Looks like that is how relative references are supposed to work. If you want sticky references use absolute addressing, i.e. =A$2

Comment 5 Anil Seth 2014-07-18 09:32:02 UTC
Created attachment 919005 [details]
Sorting the 3 rows on the first sheet demonstrates the problem

Sorry, I forgot to attach the modified sample sheet.

The spreadsheet showing the error now has A2 in C5 and A$2 in C6.

After sorting first 3 rows, both show the value b, 
that is they still have the same formula A2 and A$2
whereas I would have expected the result to be A1 and A$2.

Comment 6 Anil Seth 2014-07-18 09:34:28 UTC
Sorry, it should be:
The spreadsheet showing the error now has A2 in C5 and A$2 in D5.

Comment 7 Eike Rathke 2014-07-18 13:33:52 UTC
Ah, I see what you mean. That's something different though, not related to the sheet reference issue here, and apparently fixed in a later version. Upcoming 4.3 behaves differently.

Comment 8 Anil Seth 2014-07-18 17:04:56 UTC
OK. Thanks.

Comment 9 Fedora Update System 2014-07-19 06:03:40 UTC
libreoffice-4.2.5.2-6.fc20 has been pushed to the Fedora 20 stable repository.  If problems still persist, please make note of it in this bug report.


Note You need to log in before you can comment on or make changes to this bug.