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.
Thanks.
You should now be able to upload.
@ thelukester : I think it is that “bug” (an enhancement with unwanted side effects9 - And the discussion there led to 2 consequences:
-
The former behaviour was retrieved with V4.3.4
-
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?
@davymark - I made a test LibO 4.2.6.2 / 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.
(Deleted!)
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.