Refering to a named Range using a variable.

[fishoutofwater]

In column A1, B1 and C1 I have 3 names: Ted, Mary, Casper

Let's say I have named the ranges as follows:

A1 = "Person1"
B1 = "Person2"
C1 = "Person3"

Now, let's say in D1 I have the number 2
In d2 I want to do this:
=Person(D1) which would mean =Person2 which would deliver "Mary"
Now, if I changed D1 to 3, I'd want "Casper" to show up in D2.

In other words, I want to call the data by using the range name, but where I use a variable (in cell D1) to append to the first part of the range name to create the whole range name.

I am sure I can do this, but the syntax stuff I have been playing with is not working at all.

Thanks for any tips on this aspect of working with names!

 

[jim may]

Excel Workbook
	A	B	C	D
1	Ted	Mary	Casper	2
2				Mary
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D2	=INDIRECT("Person"&D1)

 

[fishoutofwater]

Thanks Jim. You make it look easy