Issue 101791 - Sorting can modify formula
Summary: Sorting can modify formula
Status: CLOSED DUPLICATE of issue 101690
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOO310m11
Hardware: PC All
: P2 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2009-05-12 00:25 UTC by bill1017
Modified: 2009-05-18 05:05 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Sample document (9.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2009-05-12 06:12 UTC, Rainer Bielefeld 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description bill1017 2009-05-12 00:25:29 UTC 
Sorting modifies cell refrences in formula, does not behave like previous 
versions.

Cells A1-A3 have values 1,2,3

Cells A4-A6 have values 1,2,3

Cell B4 has formula =$A$1+A4
Cell B5 has formula =$A$2+A5
Cell B6 has formula =$A$3+A6

----------------------------

Select cells A4-B6
Sort on Column A Decending

Now:

Cells A4-A6 have values 3,2,1

Cell B4 has formula =$A$3+A6
Cell B5 has formula =$A$2+A5
Cell B6 has formula =$A$1+A4

It should have:

Cells A4-A6 have values 3,2,1

Cell B4 has formula =$A$3+A4
Cell B5 has formula =$A$2+A5
Cell B6 has formula =$A$1+A6
Comment 1 tomwb 2009-05-12 02:05:57 UTC 
I can confirm the defect in the sort.

OOO310m11 on Vista

OOO2.4.1 on Vista and Excel03 on XP sort correctly.

TomW
Comment 2 tomwb 2009-05-12 02:17:17 UTC 
Actually I believe this is a dupe of Issue 101690, or at least related to it.

TomW
Comment 3 Rainer Bielefeld 2009-05-12 05:59:04 UTC 
I checked with "Ooo 3.1.0 multilingual version German UI WIN XP: [OOO310m11
(Build 9399)]" and can confirm the reported effect. 

The formula is wrongly treated as if the second therm after "+" also has a "$"
in front of the numeric.  

I attached a sample file "test310.ods" similar to reporter's, but with
additional alphanumeric columns to ease orientation (select 'A1:D6' before you
sort. In sheet "Step 1" you see situation before sort, in sheet "Step 2"
situation after sort, "Step 3" has been sorted with 2.4.1

That worked well with "2.4.1  Multilingual version German UI WIN XP:
[680m17(Build9310)]"

I am pretty sure that I already saw an Issue concerning similar problems, but
currently I can not find it.

This problem is very serious, can make OOo CALC unusable for many applications.
Comment 4 Rainer Bielefeld 2009-05-12 06:11:41 UTC 
All WIN (and I believe: all OS)
Comment 5 Rainer Bielefeld 2009-05-12 06:12:56 UTC 
Created attachment 62179 [details]
Sample document
Comment 6 niklas.nebel 2009-05-12 09:40:01 UTC 
duplicate

*** This issue has been marked as a duplicate of 101690 ***
Comment 7 niklas.nebel 2009-05-12 09:43:30 UTC 
closing duplicate
Comment 8 jbf.faure 2009-05-18 05:05:19 UTC 
In such case you have a workaround : replace 
B4=$A$1+A4
by
B4=$A$1+index($A$1:$A$6;ligne();1)

Ok, it's not easy but it works, even in OOo 3.1

Regards
JBF