Using INDIRECT with named ranges

Hello all,

I have a named range called List_To_Show, defined as =INDIRECT("List_"&List_Sel). List_Sel is either "1" or "2". The named ranges "List_1" and "List_2" are static.

If I use List_To_Show in an INDEX function as follows, the function works: =INDEX(List_To_Show,2,2). However, if I try to use it in an index function in conjunction with INDIRECT, it fails: =INDEX(INDIRECT("List_"&"To_Show"),2,2). As far as I can tell, the two should produce exactly the same result. Can anyone explain why the second one fails?

For reference, I have attached the spreadsheet I'm using. It needs to work in Excel 2003 and later versions. Thanks in advance for your help!


BTW, I realize this issue may seem trivial. I created this simple example to illustrate a problem I'm having with a much more complicated spreadsheet.

Thank you both for the responses. Unfortunately, I haven't been able to solve this problem yet and would appreciate your continued advice.

What I would like to do is build a particular named range as a text string (using the contents of certain cells) and have that particular named range refer to one of multiple other named ranges. I've attached a new spreadsheet with a more complicated example to try to illustrate the situation.

In this new example, "List_Current" is a range that refers to either "List_Current_A" or "List_Current_B" and "List_Future" is a range that refers to either "List_Future_A" or "List_Future_B". The determination of A or B and the determination of Current or Future are made by referencing cells in the worksheet.

The formulas highlighted in yellow illustrate what I would like to do. What I don't understand is why directly using the named range List_Current works (as done in cell L14) but using INDIRECT to reference "List_Current" does not work. Based on the Excel help and my own past experience, this seems exactly what INDIRECT is intended to do- return a reference specified by a text string.

Any suggestions? If you have alternate approaches to tackling this I would enjoy hearing them as well. I would like to avoid building the text strings as "List_Current_A" and "List_Current_B" because my master spreadsheet was constructed to reference only "List_Current"- hence my desire to work with that name only rather than each of the possible names that it might refer to.

Thanks!