May 08 2021 08:59 PM
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. I would like to be able to sort the rows for AB&C but wish to keep the content "raff 56 w" in the F2 cell regardless of where it end up in the sort. Meaning if "raff 56 w" in now moved down to row A13, B13 & C13, I wish Cell F2 to pull the information from there. Basically following where ever it sorts and maintaining the link. Is that possible and if so may someone tell me how it is done please?
thanks so much
May 09 2021 01:43 AM
SolutionThat 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.
May 09 2021 03:07 AM
May 10 2021 02:09 AM
May 09 2021 01:43 AM
SolutionThat 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.