Cell reference into sorted table not changed when sorting
If a cell references Table 1::B2 and if I then sort Table 1::B, the value of the cell changes to the new value of B2. This is by design. It doesn't matter if I use B2 or $B$2. MS Excel has the same behaviour.
To clarify: Let's say Table 1::B have 3 cells, with values 1, 2, and 3 respectively. The cell reference to B2 first hence has the value 1, and after descending sort of Table 1::B, the referencing cell has the value 3.
However, I want the behaviour where the cell reference continues pointing to the cell, and hence changes to its new place. After the sort the value hence still is 1, because it references the "same" cell.
How do I achieve this behavior? It might be I'm misusing the spreadsheet in a wrong way here, it's perhaps a question of spreadsheet design:
My Table 1 is ingredients which are sorted by price. Sometimes I add new ingredients, but other tables reference specific ingredients' cells. One workaround is to VLOOKUP the ingredient by name, such that it's not affected by sorting. Not perfect because it hard codes the relative column, but better.
Using Numbers 14.4, macOS Sequoia 15.6.1.
MacBook Air 13″, macOS 15.6
