Sorting a Table with relative references

[StuartR]

I have a table with two columns. The table is in columns A and B of a worksheet called "Categories"
The first column, "Category" is fixed text.
The second column"Date used" is calculated from category. The formula in cell B2 looks like this... =MAXIFS(Transactions!$C$10:C$25000,Transactions!$B$10:B$25000,Categories!$A2)

My problem is that I want to sort the table by Date used. When I do this the relative references (Categories!$A2) do not update to match the new row that the cell is in. So all the dates are in the wrong rows.

[HansV]

I assume that you either selected a cell in the Date Used column or the entire table before you sorted.
If you do it that way, the categories and dates will be moved together, and the formula in B2 should still refer to $A2.

If you just sort the date column, the dates won't match the categories anymore.

S0446.png

SortTable.xlsx

[StuartR]

I tried selecting the whole table, as well as selecting a single cell in the table, but it didn't help...

Sort problem 1.png

Sort problem 2.png

Sort problem 3.png

[HansV]

Ah - try removing "Categories!" from "Categories!$A2"

=MAXIFS(Transactions!$C$10:C$25000,Transactions!$B$10:B$25000,$A2)

[StuartR]

Brilliant, that fixed it, thank you!