Referencing INDIRECT named ranges in graphs

[long0281]

Trying to find an easy way to update a set of graphs based on a scenario selected from a drop-down (each scenario references a different range of cells; same number of rows and columns in each scenario, but different values.)

I created a dynamic named range using the INDIRECT function, but when I reference the name in the Series formula, I get an error message. I've tried different syntax (e.g. referencing the sheet name in the named range vs. the Series formula,) but I can't seem to get past the error message.

I stumbled on this Q&A. Apparently it can be done. I've had no luck though.

can I use an indirect type formula within a chart data range? - Microsoft Community

Is anyone able to replicate this functionality?

 

[Jon Peltier]

This may depend on the Refers To formula, which you have not provided.

 

[long0281]

Thanks for the reply. The Refers To forumla is =INDIRECT(Sheet1!$Q$4). Cell Q4 contains the following: $D$16:$H16.

What I want, is to be able to change $D$16:$H16 to $E$16:$E16 (for example,) and have the graph reference that row instead of the one above it.

Please let me know if you need me to provide any additional information..

 

[Jon Peltier]

You need to reference the range with the worksheet. Put Sheet1!$D$16:$H$16 into Q4.

There are more robust ways to let a user select the range displayed in a chart. Names are freaky, difficult to create, prone to error, and hard to maintain. Plus, for the INDIRECT, it's a pain to type an address into a cell.

I show a few simple examples of interactive charts in my tutorial Simple Interactive Charts.

 

[long0281]

Yeah, I tried that too.. Still get the error message.

I'll take a look at your Interactive Charts tutorial; seems like a better approach anyway.

Thanks again for reply. Your blog posts have been extremely valuable (especially the waterfall chart utility!)