SOLVED

Referencing a Defined Name in a Graph

Copper Contributor

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!!

7 Replies

@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

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")

@terraficke 

See the attached demo.

@Hans Vogelaar THANK YOU!! You are amazing.

 

It looks like I was following your steps correctly. I think my issue is that my defined names contain OFFSET functions.

 

I attached an edited version of what you sent me with the edited ranges - after this change it no longer works. Any ideas?

 

You have been SO helpful<3

@terraficke 

image.png

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.

best response confirmed by Sergei Baklan (MVP)
Solution

@terraficke 

A new attempt:

@Hans Vogelaar Oh wow, this is so much better. Now I only need the two defined names "Dates" and "Source". This is working perfectly for me. You are a life saver!!

1 best response

Accepted Solutions
best response confirmed by Sergei Baklan (MVP)
Solution