Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 27 May 2015 00:46:25 +0200When I sort selected cells the sort changes formulas in unselected cellshttps://ask.libreoffice.org/en/question/40632/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.
Thanks.
Fri, 03 Oct 2014 18:58:20 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/Comment by Lupp for <p>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. <br/>
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. <br/>
Thanks.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?comment=40882#post-id-40882You should now be able to upload.Thu, 09 Oct 2014 12:57:33 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?comment=40882#post-id-40882Answer by thelukester for <p>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. <br/>
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. <br/>
Thanks.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=40870#post-id-40870Is this [Bug 81633](https://bugs.freedesktop.org/show_bug.cgi?id=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.
Thu, 09 Oct 2014 06:24:10 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=40870#post-id-40870Comment by Lupp for <p>Is this <a href="https://bugs.freedesktop.org/show_bug.cgi?id=81633">Bug 81633</a> - 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.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?comment=51161#post-id-51161(Deleted!)Wed, 27 May 2015 00:27:58 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?comment=51161#post-id-51161Answer by DenisB for <p>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. <br/>
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. <br/>
Thanks.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=51152#post-id-51152Perhaps 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.
Tue, 26 May 2015 18:10:01 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=51152#post-id-51152Answer by Lupp for <p>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. <br/>
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. <br/>
Thanks.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=51163#post-id-51163@ 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?Wed, 27 May 2015 00:46:25 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=51163#post-id-51163Answer by ROSt52 for <p>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. <br/>
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. <br/>
Thanks.</p>
https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=40651#post-id-40651@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.
[ForumlaTest.ods](/upfiles/14123948206527703.ods)Sat, 04 Oct 2014 05:52:10 +0200https://ask.libreoffice.org/en/question/40632/when-i-sort-selected-cells-the-sort-changes-formulas-in-unselected-cells/?answer=40651#post-id-40651