Ask Your Question

When I sort selected cells the sort changes formulas in unselected cells

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 close merge delete


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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 669 times

Last updated: May 27 '15