Excel - reading a named range into a formula

[TFlanagan]

Good morning. I have a worksheet with several named ranges. On a second sheet I am constructing a chart whereby the user can enter one of the named ranges and the chart automatically updates with the data pertinent to that particular named range. The difficulty I have is in looking up the data. When I hard-code my named range into HLOOKUP, it works correctly.
ex: =HLOOKUP(B1,NAMEDRANGE,2,FALSE)

But when I attempt to refer to the named range via the cell the user enters the named range into I get a VALUE error.
ex: user enters NAMEDRANGE into cell A1. The formula
=HLOOKUP(B1,A1,2,FALSE) evaluates A1 as “NAMEDRANGE” (text) and not as a named range.

How can this be accomplished?

Thank you.

 

[SkipVought]

hi,
[tt]
=HLOOKUP(B1,INDIRECT(A1),2,FALSE)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TFlanagan]

That was one of the things I tried. Excel evaluates INDIRECT(A1) as INDIRECT("NAMEDRANGE") which then goes to
=HLOOKUP(B1,#REF!,2,FALSE)

What Excel needs to do is somehow read the text that is entered in A1 not as text but as a named range. Is there a UDF maybe that does that?

 

[SkipVought]

What you need to have in A1 is the NAME of the range that is selected for the chart.

Personally, I would not use multiple named ranges.

I would use ONE named range and FILTER the data to be displayed on the chart.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

TFlanagan, is the "NamedRange" spelled correctly? because this method does work.