Forum Discussion
Exel
How do I keep referencing content in the cell even after sorting?
5 Replies
ohadeini as already noted by others and noticed by you, you don't want to just link to a cell location since the values will move but that cell location will stay there. Instead you should use a unique id for that row (or add one if needed) and then use the VLOOKUP function to get the value you want. If you need an example we can make one or you can send your workbook because then we might find additional tips that might be helpful.
This same question was asked recently by another person. I don't think it's actually possible because the act of sorting moves the data but not the reference. That's true even if you were using a named range.
So then several questions arise. Here are two:
- What's the nature of that reference to begin with? If it's some kind of constant value that needs to be referenced by other operations (as contrasted with other cells in the same column of the table that is being sorted), then are you doing the same thing with other cells in that table or column? If it's a constant (or variable) that you make reference to elsewhere in the workbook, maybe it should be stored outside of this column/table.
- Why are you sorting this particular column/table? It's possible to extract the data (using some of the new Dynamic Array functions (FILTER, UNIQUE, SORT) and manipulate it elsewhere in your workbook, all the while leaving the source database itself untouched.
"the act of sorting moves the data but not the reference. That's true even if you were using a named range."
Not if the reference includes the sheet name.
In fact, one of my top Excel 'pet peeves' is the fact that, when entering a formula which includes a reference to another sheet, upon switching back to the sheet in which the formula resides, the default behaviour is to now include the sheet name within any references to that sheet. And this, if unnoticed, can cause undesirable results when sorting.
Regards
Hmmm....what you say is not my experience.
Here's a very simple example. On sheet 1 I've entered a random series of numbers.
Sheet 2 contains a reference to cell C6 on sheet 1, and that reference includes the sheet name
If you sort the array on sheet 1 into numerical order, the reference continues to refer to C6, which now has different content.
This is I think the problem that the OP was referring to. What I see you as saying is that it doesn't happen if the sheet name is included, but it necessarily IS included when it's on a different sheet. For that matter, it happens on the same sheet as well, where sheet name is not part of the reference.
So help me decipher your disagreement. Or are we saying the same thing in different ways and somehow misunderstanding one another.
I HAVE also experienced difficult and confusing results when sorting a table with formulas in the table itself.... so it may be that I share your very pet peeve.
