Forum Discussion

terraficke's avatar
terraficke
Copper Contributor
Sep 12, 2023

Referencing a Defined Name in a Graph

Hello Friends,

I am trying to create an interactive line graph in a report.

I created defined name ranges for the data I want to showcase in my graph. However, my idea is to have a drop-down list where the user can select their name and my graph will select the similarly named range for its data.

My defined ranges are the last names of the users and match exactly to the values in my drop-down list.

My current series values formula is (one user example):

='Sales Overflow VDN Report.xlsx'!Borkowski

*Borkowski is a single user name as well as the name of one of the defined ranges)*

My thought was to use an indirect function to allow the named range to be edited:

=INDIRECT("'Sales Overflow VDN Report.xlsx'!"&$C:$25)

where cell C25 is where my drop-down list is located

*Sales Overflow VDN Report is the name of my excel document*

Unfortunately, this is giving me an error.

Any help or suggestions are welcome!

Thank you!!

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 12, 2023

    terraficke 

    Create a new defined name Source that refers to

    =INDIRECT('SheetName'!$C$25)

    where SheetName is the name of the sheet that contains the cell with the drop-down.

    Then set the y-values of the series to

    ='Sales Overflow VDN Report.xlsx'!Source

    • terraficke's avatar
      terraficke
      Copper Contributor
      Sep 12, 2023
      If "Source" refers to my drop down cell, and my y-axis values refers to that as well, where does the reference to my named ranges come in? I might be misunderstanding:(

      All my named ranges are all on the same sheet (currently named "Sheet1"), while my drop down cell and graph are located on my main sheet (currently named "SOF Report")
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Sep 12, 2023

        terraficke 

        The SERIES function that creates charts is very limited in terms of what it will accept.  In particular, any formula must be hidden within a defined Name (here I have defined a sheet-local name 

        "Sheet1!selectedSeries").  There are a number of ways in which the name may be associated with a data range, including your idea of using INDIRECT which accepts a string and returns a named range.  SWITCH would do a similar job, as would XLOOKUP.

Share

Resources