Hyperlink Legends to Highlight a Series

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: