Naming Cells Using Relative Cell References

I

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".
 
C

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using an offset formula for the reference in a relative reference 6
Indirect function and relative references 4
Retaining cell reference in destination, when sorting source file 3
Confused about relative references in named formulas 1
range label works in VLOOKUP except if refenced in another cell 3
Help with converting a block of cells with Absolute and mixed references to relative references 3
Copy and Paste of Relative Cell References 1
Using icon sets with relative references 0

Top