# How to sort rows in Numbers without affecting the formula?

I have a table with a basic formula:

The 7th column (`1038€94`) is calculated with the formula `1133€34 - 94€40 + (empty cell)`. In others words, the 7th column is calulated by `(value in the row above) - (value of the 4th cell of the row) + (value of the 5th cell of the row)`.

All the rows have the same formula in the 7th column.

The first column is a date. If I choose to sort the rows depending on this date, the formula breaks because Numbers keeps a pointer to the cells before sorting the data:

Is there a way to sort the rows without breaking the formula? Or make the formula uses the cell above without specifying the line in the formula?

Thanks

I think there is no easy solution to this within Numbers. You should either copy your table to another sheet as values (formula results) whenever you need to sort it or recreate your formulas using, for example, the vlookup() function (also check the hlookup() and other reference functions in Numbers in case necessary) if you need to sort in-place. In the 2nd solution, instead of getting 1133,34 by a direct reference to its cell, you would get it (and any other numbers not at the same row in the formula yielding 1038,94) through a separate "vlookup" each. In addition, all "vlookup"s should be created sort-proof specifically against column-based sorts.

By column-based sort-proofing, I mean the cell references within "vlookup"s should directly refer only to the cells in the same row or the whole table. Then, assuming the formulas in the column where 1133,34 is calculated are parallel, you can use the same formula for all the cells in the same column by copy & pasting it.

Below is an example formula if you wish to take the 2nd option. It simply adds two values from the "some variable" column. The first value is from the current row and the second from two days earlier. Pls note the exact use of absolute cell references in the formula (the \$ signs, necessary so that the same formula can be used in other cells in the same column by copy & paste) and how the date for two days earlier is referenced to in the vlookup in the formula.

Here is a copy of the same formula in text in case you have trouble recreating it and wish to copy & paste from here:

``````= B5+VLOOKUP(\$A5−2,\$A\$2:\$C\$5,2,0)
``````

And here is how the table and the same formula looks like when the table is sorted based on the date:

• I just noticed your table has multiple rows with the same date. Then the exact solution I suggested may not work for you but maybe you can use some of the ideas in it together with some other solution to formulate something that works for you. Good luck! Mar 14, 2020 at 21:45

For cells you wish to reference in a formula using their relative position to the current cell, you'll need to construct references based on the row and column number of the current cell. You can then use the `INDIRECT()` function to get the value of the cell at that reference.

For example, suppose the formula for cell `G24` is

`= G23 - D24 + E24`,

and after sorting rows, this becomes e.g. cell `G5`, whose formula you then want to be

`= G4 - D5 + E5`,

regardless of where the original row `23` ends up after sorting. Then you can use the following formula for cell `G24`:

`= INDIRECT( "G" & (ROW()-1) ) - D24 + E24`.

Thus, after sorting rows, when `G24` becomes e.g. `G5`, the formula becomes

`= INDIRECT( "G" & (ROW()-1) ) - D5 + E5`,

which gives you the desired result.

For maintaining relative references involving columns potentially changing order, you can use the `RxCy` cell reference notation, where `x` is the row index and `y` is the column index. To use this, you must specify `FALSE` (or equivalent, e.g. `0`) as the second argument to `INDIRECT()`.

For example, if cell `E7` has formula

`= E6 + D7`

Then you can make all (row and column) references relative using the following formula instead:

`= INDIRECT( "R"&(ROW()-1) & "C"&COL(), FALSE ) + INDIRECT( "R"&ROW() & "C"&(COL()-1), FALSE )`.

If you are not using the dollar sign in your formulas sorting should maintain your correct algebra. If you click on a formula cell, you see a small colored box with a pulldown symbol on it. See the image below. I'm referring to the blue box.

If you click on the downward pointing triangle you get the followin pane:

Make sure neither check boxes are checked.

• Use of `\$` (or the Numbers equivalent options "Preserve Row/Column") is unrelated to OP's question, since `\$` only affects how cell references are transformed when copy–pasting a cell Mar 14, 2020 at 20:23
• It also affects it when sorting. If you have \$B\$2 then the formula will only look in B2 for a value. If the value gets moved to C5 the formula will still look in B2... Mar 14, 2020 at 23:41