December 1, 2020
I created a table and have some cells that use a formula to calculate the entry.
When I sort the table, the formula stays pointed to the cell I made before the sort, even when I name the cells.
Rows (15, 18, 20, 23), Column E, each cell is named.
Rows (15-24) reference on of the cells that is named in Column E.
C24 references E23 (XP_Sline)
When I sort the table by "Line Type" and "Test", now C29 references E23, when it should reference E24.
I tried F4 on the formula, but that did not work.
V-line XP should calculate to 9080, When sorted it changes to 7264 because the value in E23 changes.
All help would be appreciated.
Test file attached.
November 1, 2018
December 7, 2016
Nothing wrong happening, you are referencing to the cells in the worksheet, not to the data in the table. It is better to add the reference names to a new column in the table and then use XLOOKUP or INDEX and MATCH to find the row and value. I would strongly advice not to have different formulae in same table column.