mhklein57 wrote:I want to sort only the numbers in Column C

I'm assuming that statement means that the OP is using "Current selection" instead of "Extend selection" when prompted (

The cells next to the current selection also contain data …). Since the OP didn't supply details about how "the numbers aren't sorted properly" (

what happened? and what was expected?) nor any sample data, I had to make my own assumptions about the problem. So I created:

- Code: Select all Expand viewCollapse view
` A B C`

1 5 0 =A1-B1

2 5 1 =A2-B2

3 5 2 =A3-B3

So C1:C3 has values 5, 4, and 3. With OOo 3.2, when I select C1:C3, then Data > Sort > Current selection (using the default Ascending Sort), I find that after the sort, C1:C3 still has

=A1-B1,

=A2-B2, and

=A3-B3 with values 5, 4, and 3!! This is rather unexpected, but is a natural consequence of the behavior discussed in the link provided in my last post.

Here's what happened. Calc looks at the values 5, 4, and 3 and decides that to be in ascending order C1 should move to C3, C2 can stay where it is, and C3 should move to C1. So formula

=A1-B1 in C1 will be moved two rows down to C3 and

all the references in it will be adjusted accordingly (two rows down) thus becoming

=A3-B3. And formula

=A3-B3 that was in C3 before the sort will be moved two rows up to C1 and its references adjusted two rows up to become

=A1-B1. Thus the new contents of C1:C3 are

=A1-B1,

=A2-B2, and

=A3-B3, exactly same as before the sort. So the sort

was performed but the values shown for those cells remain 5, 4, and 3 even though the cells were sorted in ascending order.

Other tests on OOo 3.2 with different formulas show results consistent with the explanation of how formulas sort given in the

Sort with inter-row references topic. Because it will be difficult for users to predict how row references will be adjusted when the user tries to sort formulas, I believe the only practical method is to convert formulas to values before sorting.

It's possible that OOo 3.3, AOO 3.4, LibreOffice, etc. handle formula sorting differently, however I don't see anything about sorting changes in the release notes, and I am assuming that their behavior is the same as OOo 3.2 in this situation. Perhaps others can confirm or refute the assumption that other releases sort the formulas in my example as I have described.

acknak wrote:sort the data along with the formulas and it should work correctly.

If we change the example to (note that A1 is now

=A3-B3 and A3 is

=A1-B1)

- Code: Select all Expand viewCollapse view
` A B C`

1 5 2 =A3-B3

2 5 1 =A2-B2

3 5 0 =A1-B1

we get #REF! errors when we sort column C (values 5, 4, 3), whether we use Current Selection (column C only) or Extend Selection (sort data along with column C).

=A1-B1 is moved to C1 and its references are adjusted two rows up, causing the error.

Mr. Programmer

AOO 4.1.7 Build 9800 on MacOS 10.14.6. The locale for any menus or Calc formulas in my posts is English (USA).