Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I solved the issue, but I'm wondering if there's a better way to do this. What I have is a column of some named ranges ("NAME"), and I want a formula to the right that looks up the named range and gives me the cell reference ("C352").

I eventually solved it with this:

=CHAR(64+COLUMN(INDIRECT(C2, FALSE)))&ROW(INDIRECT(C2, FALSE))

The CHAR part is to translate the column number (3) to the letter (C), and it doesn't work past column Z.

This shouldn't be the best way to do it. I saw some VBA solutions, is that the only possible way to do this better?

share|improve this question
up vote 1 down vote accepted
=ADDRESS(ROW(INDIRECT(C2)),COLUMN(INDIRECT(C2)),4,1)

How it works

  • INDIRECT(C2) returns a reference address specified by C2. The range refered to by the named range in this case.
  • ROW( ... ) returns the row of the cell specified
  • COLUMN( ... ) ditto for column
  • ADDRESS( r, c, 4, 1) returns the address or row r, column c. 4 means relative (ie no $'s. 1 means A1 style. So ADDRESS(ROW( ... ),COLUMN( ... ),4,1) will be the address of the named range in A1 notation
share|improve this answer

If you can live with $ in the reference you could use:

=CELL("address",INDIRECT(C1))

or:

=SUBSTITUTE(CELL("address",INDIRECT(C1)),"$",)

to drop the $

share|improve this answer
=ADDRESS(ROW(INDIRECT(C2, FALSE)), COLUMN(INDIRECT(C2, FALSE)), abs_num)

where abs_num is 1 to return an absolute address (e.g., "$C$352"), 4 to return a relative address (e.g., "C352"), or 2 for "C$352", or 3 for "$C352".  (abs_num defaults to 1 (i.e., absolute) if omitted.)

share|improve this answer
    
This is the most direct way to get this? There's nothing like =ADDRESS(C2) or similar? – Ehryk Sep 13 '12 at 23:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .