Dynamically hyperlink chart labels?

Question

Hello,

As seen in the image below I have a pivot chart 10 categories in it. This pivot chart is controlled by slicers so the data and category labels are dynamic will change depending on what slicer is selected.

What I would like to do is have the data labels (category 1, category 2, etc) be hyperlinks. When the hyperlink is clicked, this will take the user to another page in the workbook.

I'm not sure where to begin with this. I think this is something that would need to be done in VBA, but I'm not sure where to start with this. Can anyone assist or give me a pointers on where to begin?

Answer 1

Here's what I've worked up so far, it's a little hacky but seems to be workin:

I have my pivot table. I create a NEW TABLE linking to the pivot table cells. then i create a regular part based off that that newly made table and cut off the axis and extra formatting and line it up to cell size. then for the data labels I just use a reference to the cells names in either the pivot table or newly made table, doesn't really matter. And it works! 

 Slicers work and everything!

But now do i hyperlink the new chart label name? Because these chart labes are referencing the pivot table, they will move around on the filter, so how can i establish the hyperlink to reference "category 1" and not just the cell name of f22. do you know what i mean?

Each category name has its own separate hyperlinks, so the actual link won't know which category name you are referring to when you click it.

Answer 2

Re:  hyperlinks to data labels

You can hyperlink to a chart, but not to the data labels. (untll proven differently)

You could place clear rectangles over the data labels and hyperlink from them.

Another way, if the chart is embedded, is to create a hyperlinked cell list adjoining the chart.

I can conceive of a VBA approach that would read the chart name or chart properties (when it is clicked) and change the cell hyperlinks accordingly. - I will not be attemping that.

(maybe, if you can get me a date with Brie Larson). <grin>

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)