Using the value in a cell as part of the name of a range in a index / lookup

[uga05]

I have a series of named ranges which denote Mondays in each year. Each begins with the letter d and follows this format: d01.06.13, d01.13.13, d01.20.13, etc.

I have another series of named ranges which denote physical locations: Room100, Room200, etc.

I have a series of columns where the top row in each contains the dates of each Monday: 01.06.13, 01.13.13, etc and rows which begin with the names of the physical locations. )Please ignore the periods below, had trouble getting my example formatted to display legibly.)

01.06.13..... 01.13.13 .....01.20.13​

Room 100 .....81% ............65% ...........78%
Room 200 .....62% ............75% ...........80%

I use the following formula (solution found on this awesome website) to return the values at the intersections of my named ranges. For example:

=(INDEX(d01.13.13,ROW(Room100),)) --> returns 65%; works perfectly, but I have to manually enter the named ranges in my formulas.


I would like to be able to use the value of the cell at the top of each column in the named range to dynamically populate the name of the range in my formula. I also need to add the letter "d" to the beginning of the name range reference I cant figure out the correct sytax, but it would be something like this:

=(INDEX("d"&C1,ROW(Room100),)) I am trying to stick the letter d combined with the value of the cell at the top of each column in place of manually typing the name of the range, which in this case is d01.13.13

Any ideas?? Many thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe try:

=INDEX(INDIRECT("d"&TEXT(C$1,"mm.dd.yy")),ROW(Room100))

 

[Aladin Akyurek]

It's not clear why you would need such an unmanageable machinery. That said:

Does this work:

=INDEX(INDIRECT("d"&C$1)) Room100

to get 65% instead of setting up:

=INDEX(INDIRECT("d"&C$1),ROW(Room100))

 

[uga05]

Thanks, this did the trick:
=INDEX(INDIRECT("d"&AZ1),ROW(Room100))

I have never used the INDIRECT function, I will have to read up on it.

As to your question about not using the ROW function, I need to index values at the intersections of ranged named rows and columns and do subsequent VLOOKUPs and other manipulations based on their content. Named ranges seemed the most efficient (well Access would better suit my needs, but I am even worse at Access than Excel!). I wont pretend to understand why I need the ROW function in this formula, but I cant get it to work w/o it.

Thanks again - I have solved dozens of little Excel problems thanks to Google and this great forum over the years. This is the first time I have ever had to ask a question.

 

[Aladin Akyurek]

Thanks, this did the trick:
=INDEX(INDIRECT("d"&AZ1),ROW(Room100))

I have never used the INDIRECT function, I will have to read up on it.

As to your question about not using the ROW function, I need to index values at the intersections of ranged named rows and columns and do subsequent VLOOKUPs and other manipulations based on their content. Named ranges seemed the most efficient (well Access would better suit my needs, but I am even worse at Access than Excel!). I wont pretend to understand why I need the ROW function in this formula, but I cant get it to work w/o it.

Thanks again - I have solved dozens of little Excel problems thanks to Google and this great forum over the years. This is the first time I have ever had to ask a question.

You are welcome.