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.

Chart with hyperlink legend

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

clip_image002[9]

Now we have a nice chart legend that highlights the data series when I click on it:

image

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”

  1. 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.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.