We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

When I sort selected cells the sort changes formulas in unselected cells [closed]

asked 2014-10-03 18:58:20 +0200

davymark gravatar image

I have a spreadsheet that has several calculations in different cells. I originally used a different (but very similar) spreadsheet program and this never happened.
What happens is I highlight columns B-I and sort according to column D. Columns J and K have formulas in them, but I don't want them to sort. But when I perform the sort the formula gets changed. I.E. the formula as written could be =SUM(H30-A30) after sort what ever was in B30 "takes" the 2nd part of the formula with it. So the formula on row 30 would now read =SUM(H30-A39) (row 39 being the new location of the data from B30 after the sort). So what I need is a way to lock individual columns out of the sort.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-21 19:32:52.937649


You should now be able to upload.

Lupp gravatar imageLupp ( 2014-10-09 12:57:33 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2015-05-26 18:10:01 +0200

DenisB gravatar image

Perhaps related : when I sort (?) a file (lets say lines 10-10.000), a formula in line 1 is changed. Not sure if after sorting since I see the change only when I need to use that formula.

From ="vers "&P1-O1&" (rec. "&P1&")"

To ="vers "&#REF !#REF !-#REF !#REF !&" (rec. "&#REF !#REF !&")"

My workaround :

Commenting a copy in the next line, i.e.

! ="vers "&M1-L1&" (rec. "&M1&")"

I have other files with similar unwanted changes in a formula, and I sort these files from time to time.

edit flag offensive delete link more

answered 2015-05-27 00:46:25 +0200

Lupp gravatar image

updated 2015-05-27 00:49:51 +0200

@ thelukester : I think it is that "bug" (an enhancement with unwanted side effects9 - And the discussion there led to 2 consequences:

1) The former behaviour was retrieved with V4.3.4

2) A new option 'Update references when sorting range of cells' was created but not set by default. The option is placed on the tab 'General' for 'Calc'.

@DenisB : Whats your version?

edit flag offensive delete link more

answered 2014-10-09 06:24:10 +0200

thelukester gravatar image

updated 2014-10-09 09:10:51 +0200

Is this Bug 81633 - Sorting shouldn't always automatically adjust references. If that's not your problem, please use the "attachment" button to provide us with a sample to reproduce this issue.

edit flag offensive delete link more



Lupp gravatar imageLupp ( 2015-05-27 00:27:58 +0200 )edit

answered 2014-10-04 05:52:10 +0200

ROSt52 gravatar image

updated 2014-10-04 05:53:56 +0200

@davymark - I made a test LibO / XP but cannot confirm a change in a formula. U attached my test file it would be helpful if you look at the file an compare with your file to identify differences. My sorting range was B and C.


edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-10-03 18:58:20 +0200

Seen: 836 times

Last updated: May 27 '15