Forum Discussion
Referencing a Defined Name in a Graph
A new attempt:
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
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")
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.
See the attached demo.
HansVogelaar 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
A new attempt:
