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

JTesche

New Member
Joined
Dec 17, 2013
Messages
3
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top