Reference a cell in a named range

[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)?

 

[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)