After sorting the table, all my formulas' referencing gets jumbled up. Is there a way or a workaround to avoid this? Weird that this is still in an issue in 2024?
Not very skilled in spreadsheets, but can follow if steps provided.
Thanks in advance for your help!
Mac Studio (2022)
Okay, now I see what you are getting at. Each row of a table is a treated like a "record" in a database and the entire record stays together during a sort. Your goal is to leave part of the record behind in its original row.
In Excel you can sort part of the table, excluding some columns. Numbers does not have that capability. One solution is to put that column of references in a different table. The two tables can be butted up together to look like one. Another solution is to lift that column from the table before sorting then put it back in afterward. It takes a few steps to do this so might not be practical if you are sorting a lot. There may be other solutions but I think it will depend upon whether your cell references follow any kind of pattern that can be recreated with formulas (using OFFSET, INDIRECT, etc., perhaps combined with ROW) versus them being kind of random.
We probably ought to hang back until the OP tells us more about what they are trying to do. We appear to be interpreting the problem different ways and our solutions are different because of it. Until the OP is willing to give a better description of their actual problem, we are all wasting our time.
Or, if it is necessary to have the results in the original table, add a column for the results:
(If it was not clear, Table 1-1::A has the formulas shown in Table 1-1::B)
How to avoid formulas from referencing wrong cells after sorting the table in Numbers?
