Preserving cell reference after a sort

[Scott Emery]

How do I preserve a reference to data when the data's cell is moved by a sort?

Example: F5 refers to M10 (either =M10 or =$M$10) and M10 contains "Domino's",
then column M is sorted and "Domino's" is moved to M15, and I want F5 now to
contain =M15 (and hence "Domino's") automatically, like when a new column is
inserted.

-Scott

 

[Bob Umlas, Excel MVP]

The short answer is that you can't. It's like if M10 contained "Domino's" and
then you copied another cell into M10 so that M10 now contains "Four
Brothers", there's no way F5 can still contain "Domino's". The contents of
M10 changed. Similarly, the contents of M10 changes when you sort, so F5
can't maintain the original.
A few solutions:
•Change F5 to Values before the sort;
•Use a combination of Index/Match in F5 to locate where Domino's is in the
first place (if possible, by some other locator cell, like one containing
"PIZZA". For example, if L10 contained PIZZA, then F5 coulr have contained
=INDEX(M:M,MATCH("PIZZA,"L:L",0)), and when the data sorted, F5 would still
display Domino's);
•Don't sort!
HTH