Reference a cell in a named range

N

Neal Carron

How do you reference a single cell in a named range?
I have:
A B C
1 13 =A1^2 =A$1^2
2 35 =A2^2 =A$2^2
3 51 =A3^2 =A$3^2
4 79 =A4^2 =A$4^2

Define the name "Alice" for column A
Then the array is
A B C
1 13 =Alice^2 =A$1^2
2 35 =Alice^2 =A$2^2
3 51 =Alice^2 =A$3^2
4 79 =Alice^2 =A$4^2

In a macro I need to use Alice, rather than A because I may insert
columns before A.
That's no problem, but I also need to refer to specific absolute rows
in the column.
I need column D to be:

A B C D
1 13 =Alice^2 =A$1^2 =A$1^2
2 35 =Alice^2 =A$2^2 =A$4^2
3 51 =Alice^2 =A$3^2 =A$3^2
4 79 =Alice^2 =A$4^2 =A$2^2

But in the macro, for column D, I need to refer to A as Alice, like
Alice$1^2. But that doesn't work.
How do you reference a specific row in a named column (with absolute
address)?
 
R

Rick Rothstein

How do you reference a single cell in a named range?
I have:
A B C
1 13 =A1^2 =A$1^2
2 35 =A2^2 =A$2^2
3 51 =A3^2 =A$3^2
4 79 =A4^2 =A$4^2

Try using the INDEX function. For example, to retrieve the 3 cell down (the
51), do this...

=INDEX(Alice,3)

Rick Rothstein (MVP - Excel)
 

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

Top