Naming Cells Using Relative Cell References

[Inobugs]

Can a cell be named so that when the sheet is sorted, the name refers to the
same data even though the position of the cell has changed?. I tried naming
cells with relative references (removing the "$" sign), but after sorting the
cell reference reverts to "A1".

 

[Chip Pearson]

Use the INDIRECT function. E.g.,

=INDIRECT("A1")

This will reference A1 no matter how you
cut/copy/paste/sort/insert/delete etc. Excel doesn't recognize the A1
within the quotes as a range reference, but rather sees it as a simple
string, so it won't change it when things get moved about.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)