Using a concatenated string to reference a name ranged for a chart series

So I've created a self-updating chart using named ranges with the OFFSET() function. The table works if I directly reference the named ranges i've created in the Series Value box.

What I am trying to do is give my user the ability to choose what named ranges to put into the chart by using a list box, option Box, and eventually a check box. So far I have set up List and Option boxes that return certain string values (using CHOOSE). I have been concatenating those strings to match exactly with the named ranges I have already created.

The Problem

I can't find a way for the chart to recognize the concatenated string as a reference to a named range. I have tried:

Referencing the Cell Number directly in the Series Value Box
Referencing a TEXT() version of the concatenated string
I have even just tried typing out the string to see if the chart would recognize a string in a cell as a named range. Didn't work

I feel like I'm missing something simple, or this isn't possible. Any help would be greatly appreciated.

Thanks.

Are you including the sheet name as well as the named range?

i.e. are you using
=Sheet1!Named_Range
not just
=Named_Range

Edit: At Pete, I'm not sure you can use indirect inside a chart? I'd guess you would want to set the chart reference to a specific named range and then use that named range to adjust based on the user selection:

eg. chart source: =Sheet1!Named_Range1

NamedRange1 definition: =indirect(A1), where A1 contains the name of the named range you want the chart to reference.

"However, I'm not sure if INDIRECT and OFFSET can both work together like this as they are both volatile functions"

Correct. Using INDIRECT to reference a Named Range is not valid if that Named Range uses certain formulae, including OFFSET.

Regards

you have to use an additional named range using EVALUATE in the same way as you would use INDIRECT and then refer to this named range in the chart series

no it is a native excel function and works in all versions of excel from version 4 :-)

it is not an add-in
:-)

It's NOT an Add-In!!

Edit: @JosephP

It's not a worksheet function, but can be used in Named Ranges.

it will not work in a worksheet-you can only use it in a defined name. for instance if your INDIRECT formula for a static range would be
=INDIRECT(A1)
for a dynamic range you define a name (called say MySeries1) and in the RefersTo box you enter
=EVALUATE(A1)

you're welcome :-)

A new problem has pop up! I figured I'd update the same thread rather than start a new one.

The Problem
I saved the workbook as an "Excel Macro-Enabled Workbook" but the EVALUATION function doesn't save. When I try and open the saved workbook, my series value formulas that have EVALUATION in them all show up as:
=[0]!Eval_Named_Range

Additional Info

When I try to replace the "[0]!" with a the correct tab reference, excel sends back an invalid reference error pop up box.

First, I have to go into Name Manager, open and save (without changing any data) the named range. Then I can go in and replace the "[0]!" with the correct tab reference and it works.

Opening the security warning and checking "Enable This Conent" doesn't fix the isssue.

Is there another workbook type i need to save as to make this work?

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I'm guessing I need to Enable All Macros in the Macro Security Settings. How safe is this? The file would be in the department network folder.

do you have at least service pack 2 installed? that sounds like an early 2007 named range bug

I'm referring to office service packs rather than windows ones

I would insist on it-there are a lot of bugs in 2007 so you need at least sp2 and preferably sp3 and all subsequent patches too! ;-)