So I picked up Jon’s idea and tried to combine it with ParamLink. In The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.
To highlight a data series you have to click on the legend (hyperlink).
These are the steps to create a hyperlink in a chart:
Insert a couple of ParamLink formulas next to your chart that serve as a legend.
=ParamLink(,”Series Name 1″,1,”HighligtedDataSeries”,”=”&”Series Name 1″)
=ParamLink(,”Series Name 2″,1,”HighligtedDataSeries”,”=”&”Series Name 2″)
ParamLink inserts a hyperlink, that sets the name HighligtedDataSeries when it is clicked. When you click on
=ParamLink(,”Jon’s Excel and Chart Pages”,1,”HighligtedDataSeries”,”=”&”Jon’s Excel and Chart Pages”)
the name HighligtedDataSeries is defined as =”Jon’s Excel and Chart Pages”.
Now we define a name that dynamically selects the data series based on the selection in HighligtedDataSeries.
ChrtData := INDEX(Data!$B$2:$F$65,,MATCH(HighligtedDataSeries,Data!$B$1:$F$1,0))
Now insert a new data series that refers to =<SheetName>! ChrtData
Now we have a nice chart legend that highlights the data series when I click on it:
To give the user the visual feedback about which data series is selected we can add some conditional formatting to the legend that highlights the selected legend entry:
2 Replies to “Hyperlink Legends to Highlight a Series”
Ha ha, there’s more than one way to skin a cat. Excel’s power means with a little coding (chart events for my approach, the ParamLink add-in for yours) you can do amazing things.