Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Preserving cell reference after a sort
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 |
#2
|
|||
|
|||
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 "Scott Emery" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
How do I use a variable in a cell reference? | Excel Discussion (Misc queries) | |||
how to cell reference a Chart title | Charts and Charting in Excel | |||
international absulute cell reference | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |