Hyperlinking Worksheets

Summary:

This article discusses how to add a hyperlink to link a dimension (or other field) between different sheets in a workbook.

More Information:

Hyperlink Formula

The HYPERLINK, HYPERLINK (link_location,friendly_name),  function has two components:

1. Link_location: is the location to be directed to, e.g. a cell within the worksheet or path to a specific file.
2. Friendly_Name: is an optional field which is used to determine how the link is displayed within the workbook. If this parameter is omitted, then the actual link appears in the cell instead of a friendly name.

For example, to link to an Excel file called Training which is saved in My Documents folder, the formula, with a friendly hyperlink containing the friendly name “training”, would be as follows:

=HYPERLINK(“C:\Documents and Settings\Username\My Documents\Training.xlsx”,”Training”)

Setting up a Dynamic Hyperlink Formula Between Tabs

The following will be based on a BI360 report saved as ”Linking.xlsx”. This template will allow a user to input budget values for each account on Sheet 1 and enable users to click on the account which is linked to Sheet2, which contains comparative information.  Sheet1 of this report is contains four columns, which is shown in the screenshot below:

1. Column A: contains the account number for a group of accounts based on an expanding group in row four. Note that this column will eventually be hidden.
2. Column B: will contain the hyperlink. The goal will be to show the account number as a friendly name, so the formula will refernce the account number in the adjacent cell in Column A. The formula in cell B4 is as follows: =HYPERLINK("[Linking.xlsx]Sheet2!A"&MATCH(A4,Sheet2!A:A,0),A4).
3. Column C: contains the account description
4. Column D: this will be reserved for inputting values.

Sheet2 of the report contains three columns:

1. Column A: contains the account number. This is the location where the hyperlink from the first tab will go once clicked, for the respective account chosen.
2. Column B: comprises an amount (for reference only).
3. Column C: includes a description (for reference only).

Diagnosing the Formula

Once the two tabs have been populated with the accounts and the report has been executed, then a link will be available.  The link, when clicked, will take the user from Sheet1 to Sheet2 for the respective account that was clicked on in Sheet1.  The formula works due to MATCH function within the HYPERLINK function:

=HYPERLINK("[Linking.xlsx]Sheet2!A"&MATCH(A4,Sheet2!A:A,0),A4)

The MATCH portion of the formula, MATCH(A4,Sheet2!A:A,0), matches exactly what is found in cell A4 on Sheet1,to what is found in Sheet 2 in Column A. Once a match is found then the formula will return the row number that is found in Sheet 2. In this example it would return ”4” since account 4000 is on Sheet 2, Row 4.

The HYPERLINK formula used in conjuction wtih the MATCH Formula solves the dilemna. The MATCH formula makes the hyperlink dynamic so it will update for each unique account.

=HYPERLINK("[Linking.xlsx]Sheet2!A"&MATCH(A4,Sheet2!A:A,0),A4)

The last portion of the formula is to reference the friendly name. Cell A4 is used so that it will show the account number as the friendly name in the hyperlink. As an example, column Column A can be hidden on Sheet 1 and then column B can show the account number with the hyperlink.

Applies To

• Report Designer 4.x