Sorting in Numbers seems broken, cell data isnt tracked when sorted

Question

Level 2

230 points

Sorting in Numbers seems broken, cell data isnt tracked when sorted

So I don't seem to be able to grasp how to get Numbers to sort like I believe it is supposed to.

Let me explain the issue.

I have a spreadsheet with multiple sheets that are interdependent. For this explanation ill say i have two sheets. Sheet A is a sheet with a list of spices and their $/gram. Sheet B is a recipe that lists individual spices listed in Sheet A and gets the value of each spice used by tracking the $/gram cell for each spice in Sheet A.

So sheet A may look like:

Spice $/g

pepper .02

salt .01

Sheet B looks like:

salt 2g .02

pepper 1g .02

When I sort the $/g column in sheet A in ascending order it looks like:

Spice $/g

salt .01

pepper .02

So far so good. However now my sheet B formula is still using the $/g of the actual cell and not tracking that the data for the cell has been moved to a new cell. So while in sheet A cell B3 was .01 it is now .02 which is the cost of pepper, not salt. Salts value moved to cell B2.

The formula in sheet B for salt was =B2*SheetACellB3 but when you sort its like numbers isnt smart enough to know that she value of $/grams for the Salt spice moved, it still uses the the exact same cell regardless of what spice $/g is using that cell after a sort.

Either Numbers is stupid and this is a bug or Im stupid and im not doing something right.

The latter is entirely possible. But everything I know tells me the spreadsheet should track x,y (column, row) when you sort. So that the value for x,y even if it moves, is still used by formulas that depend on that value, and NOT use a static cell to obtain the value, which moves during a sort.

I hope this made sense. I cannot figure out why it wont track the movement of a cell when sorting.

Thank you for any help you can offer here.

MacBook Pro

Posted on Oct 17, 2021 6:40 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,562 points

Oct 17, 2021 7:12 PM in response to Chris Watson1

You can do a lookup, like this:

=XLOOKUP(A2,Table 1::A,Table 1::B,"not found")*B2

Substitute ; for , in the formula if your region uses , as a decimal separator.

SG

Reply

Answer 2

Badunit

Level 7

30,714 points

Oct 17, 2021 8:29 PM in response to Chris Watson1

Interestingly, long ago Numbers used to work as you are requesting. There used to be frequent posts about how to make it stop doing that and how Excel didn't do it that way. Tracking the cells was useful for cases like yours but it could really mangle some spreadsheets. Imagine you have a formula that is =SUM(A2:A20). Back then if you sorted, it would turn into something like =SUM(A3:A4, A6, A13, A24, and so on) to account for all the cells in their new locations. We used workarounds with OFFSET like in =SUM(OFFSET(A1,1,0,19,1) so it would NOT track the cells, with the assumption that row 1 was a header row that stayed anchored. It was also a problem for simple running total formulas like a column of formulas of the form B3 = B2+A3. After a sort, "B2" would often not end up not directly above "B3" and the "running total" would be all wonky, jumping all over the place.

There are advantages and disadvantages to both ways of doing it.

Reply

Answer 3

Chris Watson1 Author

Level 2

230 points

Oct 17, 2021 9:00 PM in response to Badunit

Thank you for explanation. I was 50% certain there was a much more educated and intelligent reason that it was not working as I expected because I have no SME on spreadsheets. But I was half sure someone FAR smarter than me about the issues involved in a spreadsheet changed the behavior as you note from what I expected to the way it is now. I just didn't know why. So your explanation helps greatly in understanding the reasoning.

I felt initially like this seriously violates POLA as this is NOT the expected default behavior the end user expects. But after doing a bit of a deep dive I get why the change. So thank you for giving a little explanation of the state of this issue.

Reply

Answer 4

Chris Watson1 Author

Level 2

230 points

Oct 17, 2021 8:55 PM in response to SGIII

Thank you very much. That is exactly what I didn't understand I needed. Thank you sir!

Reply