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

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.

You should now be able to upload.

@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.


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.


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.

=“vers “&P1-O1&” (rec. “&P1&”)”

=“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.

@ 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?