Chart Change Using Excel Hyperlink Formula

This article explains how you can use an Excel hyperlink to change a cell value.  This can be used in conjunction with a chart to create a formative dashboard.  In another article I created a chart which used a mouse over event through VBA.  I was reading an article on the optionexplicitvba blog here about using a hyperlink to do the same thing.  The hyperlink in Excel uses a custom function to change the value of a cell.  Traditionally a custom function can not change a cell value or perform a macro style action, this rule is thrown to the wind inside a hyperlink.  The custom function can perform an action inside of a hyperlink.  Bonus!!!  This opens up a whole new world of charting fun because as you change one cell you can effectively change the look and feel of an entire report in Excel.  Dashboards can change, charts can change, colours can change - hell the possibilities are endless.

So lets create a file so when you put your mouse pointer on a year a chart will change and the conditional formatting will change too. 

In the above example as you hover over 2017 for example the chart will change too.  How cool.  Lets establish how this is achieved.  Firstly you need a data set which will change as a cell changes.

I have set up 4 years worth of Income related data.  This data spins or changes on a single cell.  This cell is on Sheet2 in cell F3.  Now the custom function looks like this.

Where sName is the value in the cell in Dark Blue in the table above (2012).  

The hyperlink formula is the following.

=IFERROR(HYPERLINK(YearSelect(A13)),A13)

In A13 put the year 2015, in A14 put 2016 and so on till 2019.  

To show the values along side the year which is selected above conditional formatting is used.  The file will help to show the workings in more detail.  There is a follow up article on this topic originally titled Chart Hyperlinks Part 2.