Referencing a Named Range in a Series Function for a Dynamic Chart

[JTesche]

I am trying to create some dynamic charts through the use of named ranges and the concatenate formula with some data validation lists to select which data to graph. So using the SERIES() function I am able to plot the data if I type in my named range directly into the formula (ex: Hours!AbCd) but I would like to make the named range dynamic and I am struggling to pull that in correctly. On Sheet1 I have a summary page, where I am able to select A or a & B or b & C or c etc. from my validated lists, which concatenates into cell A:10 to create the named ranges I have on the Hours Sheet. So with my knowledge my best stab at the formula is SERIES("Name", "X Axis Label", "Hours!"&Sheet1!A:10, "Order"). Hopefully that was clear, and thank you for the help!

 

[Jon Peltier]

Make a master Name, which can refer to any of the 16 combinations.

Alternatively, use a helper column, which links to the relevant values based on clever formulas you write. This will be easier to debug than a master name.

 

[JTesche]

Thanks for the reply Jon,

You lost me with the idea of a master name. I understood it as naming the cell A10 (where the selections concatenate to) and then calling that name into the SERIES() function but that didn't work. What am I missing?

I am also struggling with your second idea. I have written formulas to create the range I want to specify but the result is "text" and so when I try to reference that cell in my SERIES() function it just brings the value in the cell not the range I am trying to specify with it. Is there a way/trick to say SERIES("Name", "X Axis Label", A10, "Order"). where A10 actually evaluates to Hours!G23:AC23?

Thanks,

Jared

 

[Jon Peltier]

Just to clarify, the series formula never looks quite like this:

SERIES("Name", "X Axis Label", A10, "Order")

The series formula looks like this:

=SERIES(<name reference>,<x values reference>,<y values reference>,<series order>)

<name reference> can be empty, a string in quotes, or a fully qualified range reference*
<x values reference> can be empty, a literal array of elements in curly braces**, or a fully qualified range reference
<y values reference> can be a literal array of elements in curly braces, or a fully qualified range reference
<series order> is a whole positive number

* a fully qualified range reference must include the workbook or worksheet name and the address of a range or the name of a range:
Sheet1!$A$1
'Sheet 2'!$B$2:$B$15
Sheet3!NameWithWorksheetScope
Book4.xls!NameWithWorkbookScope

** a literal array looks like:
{"alpha","beta","gamma"} - array of text labels
{1,2,3} - array of numerical values

The references in the series formula are link references only; they may not include any calculations or functions.

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

I presume from your description that you have a number of named ranges (or "Names" in Excel parlance) in your worksheet, like AbCd for A5:A10, or aBCd for G5:G10.

You can set up a master name that could take the value of other names, based for example on values tested in if statements. You would create it using:

Name: Master_Name
Refers To: =IF($A$1="A",IF($B$1="B",AB,Ab),IF($B$1="B",aB,ab))

where AB, Ab, aB, and ab are other named ranges I'm using in this example.

So if you were using the master name as your Y value definition, your series formula might look like this:

=SERIES("My Data",Sheet1!$A$2:$A$15,Sheet1!Master_Name,1)

 

[JTesche]

Ahhh, that makes sense. Thank you for the explanation into the SERIES() function.

The Master Name method would get a little hairy in my case because I would have 2600 names with 6 types of identifiers, but it would work, just a long nest.

I Have gone a different route though. What I ended up doing was creating a new sheet with static ranges that will be charted, then just have the ranges populate dynamically with the concatenate and index-match formulas. I have done this with another sheet in the past but was hoping to trim some fat off my sheet this time.

Thank you for all the help, I'll play with the Master Name thing on a smaller sheet!

Jared

 

[Jon Peltier]

Your alternative approach is something I hinted at in my first reply, but my second reply was getting overly long. Seems you didn't need my explanation for that. I recommend you use it also for the smaller data set, since charts are most robust when directly plotting from range addresses.