Forum Discussion
Solved
How do you have a formula link, keep the data from a cell that moves from sorting?
In this scenario I have data from A2, B2 & C2 link to F2, it is a concatenate formula which makes F2 cell show "raff 56 w". Column D & E are empty so that my F column does not sort with AB&C columns....
That is just the way Excel works - sorting a range does not update references to cells in the range from cells outside that range. A workaround would be to add a column with unique identifiers, and to use INDEX/MATCH, XLOOKUP or VLOOKUP to retrieve values associated with a specific identifier.
The formula in G2 is
=INDEX($B$2:$B$5,MATCH(F2,$A$2:$A$5,0))&" "&INDEX($C$2:$C$5,MATCH(F2,$A$2:$A$5,0))&" "&INDEX($D$2:$D$5,MATCH(F2,$A$2:$A$5,0))
Because we look up the unique index 1, the result will remain the same when the range in columns A to D is sorted.
