Results 1 to 6 of 6

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

This is a discussion on Referencing a Named Range in a Series Function for a Dynamic Chart within the Excel Questions forums, part of the Question Forums category; I am trying to create some dynamic charts through the use of named ranges and the concatenate formula with some ...

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    3

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

    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!

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,673

    Default Re: Referencing a Named Range in a Series Function for a Dynamic Chart

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  3. #3
    New Member
    Join Date
    Dec 2013
    Posts
    3

    Default Re: Referencing a Named Range in a Series Function for a Dynamic Chart

    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

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,673

    Default Re: Referencing a Named Range in a Series Function for a Dynamic Chart

    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(,,,)

    can be empty, a string in quotes, or a fully qualified range reference*
    can be empty, a literal array of elements in curly braces**, or a fully qualified range reference
    can be a literal array of elements in curly braces, or a fully qualified range reference
    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)
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  5. #5
    New Member
    Join Date
    Dec 2013
    Posts
    3

    Default Re: Referencing a Named Range in a Series Function for a Dynamic Chart

    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

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,673

    Default Re: Referencing a Named Range in a Series Function for a Dynamic Chart

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com