You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Dec 3, 2023 10:00 AM

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.

Similar questions

4 replies
Question marked as Top-ranking reply

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.

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?

Sorting rows when a cell contains a formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.