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.
Attachement:
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.
Example:
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.
Example values:
V-line XP should calculate to 9080, When sorted it changes to 7264 because the value in E23 changes.
All help would be appreciated.
Thanks
Test file attached.
I think you'd be better off having a separate table for the values in your named cells.
Hello,
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.
’Br,
Anders
