Articles & Tools > The Parameter Hyperlink Add-In
Overview
The Parameter Hyperlink Add-In adds a new formula to Excel, that creates parameterized hyperlinks and sparkline hyperlinks.
The Need For Sparkline Hyperlinks
We can greatly increase the amount of information that can be included in MicroCharts dashboards by using sparklines in overview tables. You can scan thousands of figures at a glance. If KPIs are exceeding thresholds Alert Icons turn red, draw you attention, and you can zoom to a specific detail row. Seeing the trending as a sparkline causes the desire to get more detailed version of the chart with one mouse click.
The ParamLink Add-In
The Add-In implements a new Excel formula PARAMLINK that creates sparkline hyperlinks and hyperlinks in Excel cells. When the hyperlink is followed the formula can jump to details sheets and set cell values or defines names.
Installation
- Download the FREE Parameter Hyperlink Add-In
- To install the Add-In copy ParamLink.xla into the XLSTART folder located at <Programm Drive>\Program Files\Microsoft Office\<Office Version>\XLSTART.
- To use PARAMLINK with sparklines, install MicroCharts
Syntax
PARAMLINK ([linkLocation], [friendlyName], [linkType], [parameterAddress1], [parameterValue1],…, [parameterAddress13], [parameterValue13])
| Formula Parameter | Description |
| LinkLocation | is the sheet to be opened as text. Can be a text string enclosed in quotation marks or a cell that contains the link as a text string. If the jump specified in linkLocation does not a link to the cell the formula is in is used instead. |
| LinkType |
determines the link type, 1 for hyperlink and 2 for double-click. The default link type is hyperlink. |
|
Friendly_name |
is the jump text or numeric value that is displayed in the cell. Friendly_name is formatted with the style Hyperlink. If Friendly_name is omitted, the cell displays the linkLocation as the jump text |
| ParameterAddress |
is a
- reference to a cell in the targets sheet where the parameterValue when the jump is executed - Excel name if the parameter value starts with "=" |
| ParameterValue |
- the value that will passed to
parameterAdress - the name definition, must start with "=" |
Behavior and Formatting
When the users enter the ParamLink formula with linkType = 1 it is formatted as an Excel Hyperlink pointing to the sheet the ParamLink is in. To format the hyperlink the Excel style Hyperlink is used.
Examples
Increases the cell value in $A$1 and sets the current time in $B$2 on
double click.
= PARAMLINK (,"Increase A1",2,"$A$1",$A$1+1, "$B$2", NOW())
A hyperlink that shows a sparkline that ,when clicked, dynamically
updates a name with the current row.
=PARAMLINK (,MICROLINE(D3:O3,2),1,"ChartRow","="&ROW())
A hyperlink that increases the cell value in a $A$1
= PARAMLINK (,"Increase A1",1,"$A$1",$A$1+1)
A hyperlink that increases the cell value in in the Name Counter
= PARAMLINK (,"Increase A1",1,"Counter","=" & Counter +1 )
A hyperlink to the cell A1 in ‘LinkTarge’ sheet
=PARAMLINK ("LinkTarget!A1","Jump to Target Sheet")
A hyperlink that jumps to the sheet 'LinkTarget'
and sets value 'Car and Bike Stores' at the parameter reference
LinkTarget!A1.
=PARAMLINK ("LinkTarget!A1","Jump to Target Sheet and pass 'Car and Bike
Stores'",1,"LinkTarget!A1","Car and Bike Stores")
Sample Sheet
Click here to download a Sample Sheet.