Referencing a table cell, keeping reference when cell value changes

Copper Contributor

Hello,

In Excel I have a table, with the first column acting as a unique identifier (I make sure there aren't duplicates). I want to be able to reference a cell of this table's first column so that:

- if I change the value of a referenced cell, the references get updated automatically as well

- if I change the table sorting, the references to a cell with a certain value keep reference to the cell with that value

 

Is it even possible in Excel without using VBA? Thank you in advance.

3 Replies

Hi Federico,

 

Afraid that doesn't work such way. However, if you'd like for given ID to pick-up information from other columns of the table, that's INDEX/MATCH

Hi Sergei,
Thank you for your answer. The INDEX/MATCH formulas are great, but my problem is that if I change the said ID, it is not updated in the other table. Maybe I should use an additional column, using a progressive number, so that I'm sure I will never have to change? At the moment as a unique identifier I am using a name string, which is likely to be changed in the future.

Hi Frederico,

 

Yes, for unique IDs you shall to use the values which won't be changed