The Internet has introduced us to the great idea of providing more detail with just a click using hyperlinks. Now our new, free Excel add-in lets you do just that in your Excel dashboard.

As Andreas mentioned last time, our new function – the ParamLink – is perfect for drilling into the detail of a dashboard. As we’ll see in this series of posts, it can be a lot more flexible and powerful than just that. This time, we’ll create a simple example with the ParamLink acting as a method for setting some workbook variables.

The ParamLink Formula

Using the ParamLink formula doesn’t require much effort.

=PARAMLINK([linkLocation], [friendlyName], [linkType], [parameterAddress1], [parameterValue1],…, [parameterAddress13], [parameterValue13])

It allows you to specify a location to link to (for example, “A1″ with quotes will cause a jump to cell A1 when the link is clicked); the text to appear in the link cell; the type of link (1 for hyperlink, 2 for double-click); and a set of address-value pairs.

These pairs allow you to specify a cell or name (add ‘=’ to the start of the value to target a name) to be changed, and the new value to be used when the link is clicked. For a complete reference, see our in-depth article.

A Simple Example

Let’s get started by creating a data table, with an Excel chart showing a row of data. We’ll use ParamLink hyperlinks to zoom the chart to the row that we’re interested in.

We’ll start with some data that I created:

Next we’ll create some Excel names (using Insert/Name/Define…), which we can use for the chart’s data ranges:

ChrtRow	=2
ChrtName	=INDEX($A$2:$A$8, ChrtRow-1)
ChrtData	=INDEX($B$2:$M$8, ChrtRow-1,)

 

And finally we can create an Excel line chart based on those names – we’ll use =<BookName>!ChartName (e.g. =Book1.xls!ChrtName) for the series name and =<BookName>!ChartData (e.g. =Book1.xls!ChrtData) for the series data range.

Now that we have the data and chart set up, we can use the ParamLink function to activate it! In the place of the product names, we’ll use this formula:

=ParamLink(,”ProductA”,,”ChrtRow”,”=”&ROW())

…and now we have a set of hyperlinks, which drive the series in the chart.

Next time, we’ll look at a slightly more complex example where we’ll use ParamLink to allow us to link a MicroChart sparkline using ParamLink.