RD ALL How-To: Hyperlink Worksheets

Summary:

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

Article Sections:

• Method
  • Hyperlink Formula
  • Setting up a Dynamic Hyperlink Formula Between Tabs
  • Dissecting the Formula
• Properties

Method:

Hyperlink Formula

The HYPERLINK function has two components:

• Link_location: the location to be directed to, e.g. a cell within the worksheet or path to a specific file.
• 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.xlsx which is saved in the 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 Sheet1 and enable users to click on the account linked to Sheet2, which contains comparative information. 

Sheet1 of this report contains four columns, which are shown in the screenshot below.

• 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.
• Column B: will contain the hyperlink. The goal will be to show the account number as a friendly name, so the formula will reference 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)

• Column C: contains the account description
• Column D: this will be reserved for inputting values.

Sheet2 of the report contains three columns, which are shown in the screenshot below.

• 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.
• Column B: comprises an amount (for reference only).
• Column C: includes a description (for reference only).

Dissecting the Formula

Once the two tabs have been populated with the accounts and the report has been executed, a link will be available. The link, when clicked, will take the user from Sheet1 to Sheet2 for the respective account that was clicked. The formula works due to the 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 Column A on Sheet2. Once a match is found, then the formula will return the row number that is found in Sheet2. In this example, it would return ”4” since account 4000 is on Sheet2, Row 4.

The HYPERLINK formula used in conjunction with the MATCH Formula solves the dilemma. The MATCH formula makes the hyperlink dynamic so it will update for each unique account.

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 A can be hidden on Sheet1 and then Column B can show the account number with the hyperlink.

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

Properties:

Applies To

• Report Designer