Sorting rows when a cell contains a formula

Question

Level 1

8 points

Sorting rows when a cell contains a formula

Mac OS Monterey 12.5.1, Numbers 13.1

I'm trying to invert the rows in a block of cells by re-numbering the cells in the header column, then doing an "ascending order" sort. This works fine when the cells contain numbers. But the block of cells in the same row whose value comes from a formula (which reference another cell not affected by the sort) don't move with the rest of the row. Is this just the way it works when a cell contains a formula, or is there some way to get the cells with formula to also move?

Please note, the cells in the area circled in red reference cells in the block above them.

Red shows area with values from formulas before sort.

Blue shows cells with numbers moving after sort.

Posted on Dec 3, 2023 7:25 AM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,609 points

Dec 3, 2023 10:00 AM in response to Franny327

I was thinking more in the lines of function ROW, but this experiment revealed an interesting fact.

If data comes from the sorted area the formulas stay the same. But if it comes from another table, then the formulas follow the sorted rows.

Before sorting.

After sorting by column A.

If you don't care about the formulas after sorting, before sorting you could copy the area containing them and paste results (see File menu) at the same location.

Reply

Answer 2

Recycleur

Level 4

1,609 points

Dec 3, 2023 9:10 AM in response to Franny327

You should show what is the formula. It may contain some function that relies on the row number, so even if the cell itself moves, it recalculates and gives a new result based on its new location, and since all formulas are the same the result for specific coordinates is the same than before. Just guessing.

Reply

Answer 3

Franny327 Author

Level 1

8 points

Dec 3, 2023 9:32 AM in response to Recycleur

The formulas are "B2*(-1)....I14*(-1)"

So are you suggesting that if B15 references B2 before the sort, when B15 moves to B27, it now references whatever B27 used to reference (i.e. B14). And B27, which used to reference B14, is now in the position which was occupied by B15, so it now references B2? Is there some way to lock the formula reference to the cell, so that when B15 moves to B27, it's original reference to B2 moves as well?

Reply

Answer 4

Franny327 Author

Level 1

8 points

Dec 3, 2023 1:30 PM in response to Recycleur

Thank you! It looks like your experiment gives me the answer I needed - set up the reference block in a different table.

Now I just have to figure out how to sort by rows instead of columns (temporarily transpose rows and columns, as far as I can tell).

Reply

Sorting rows when a cell contains a formula

Similar questions