From the Not Just Numbers blog:

The HYPERLINK function can be very helpful in Excel for creating multiple links to websites, other documents or cells in the current document based upon cell data.

You can create a static hyperlink without it, but if you have a list of URLs or file paths, the function can allow you to dynamically create links to them without having to create each one individually and with the added advantage that the links will update if the underlying data is edited.

It’s a really simple function to use, but the Excel help function is very vague on how to use it to link to cells on another sheet in the same workbook.

First of all a quick introduction on how to use the HYPERLINK function.

It’s syntax is:

=HYPERLINK(Link address,[Friendly name])

Where the Link address can be a file path, a cell location or a URL. The Excel Help on the function gives a useful list of the syntax for each of these (except for a cell on another sheet!).

The Friendly name is optional and is the string you want to appear as the hyperlink. If this argument is not entered, the Link address will show in the cell.

Take a look at the spreadsheet below:

 

The hyperlinks in column C are created using the HYPERLINK function, the formula in C2 being:
=HYPERLINK(B2,A2)
This can then be copied down the column. Columns A and B could be hidden or on a different sheet making the hyperlinks a user-friendly way of navigating to the websites.
Now let’s say we had a Sales workbook with a sheet for each department and a Summary sheet listing all departments’ sales, with the sales total being in cell H7 on each sheet. Let us also assume that the department name is used as the tab name for each sheet.
We want the summary sheet to show three columns as below:
…with column B showing the sales total in cell H7 on each of the sheets, and column C being a hyperlink to cell H7 on each of the sheets.
We can use the INDIRECT function in B2 as follows:
=INDIRECT(“‘”&A2&”‘!H7”)
This can then be copied down.
This is the same as writing
=’Retail’!H7
Except we have used ampersands to concatenate the preceding single quote (‘), the contents of cell A2 (Retail) and ‘!H7.
The reason for placing the single quotes around the tab name is to allow for spaces in the tab name.
You would then think that you could enter the following in cell C2:
=HYPERLINK(“‘”&A2&”‘!H7”,”Visit “&A2)
NB: INDIRECT is not needed here because the HYPERLINK function expects a link in the form of a string.
or even:
=HYPERLINK(“‘Retail’!H7″,”Visit Retail”)
…but each of these return an error when you click on the hyperlink.
What the Excel Help doesn’t tell you is that when referencing worksheets in the same workbook with the HYPERLINK function, you need to prefix the sheet name with a #.
NB: If you enclose the sheet name in single quotes then the # comes before the single quote.
So:
=HYPERLINK(“#’Retail’!H7″,”Visit Retail”)
…will work. As will, for our example:
=HYPERLINK(“#'”&A2&”‘!H7”,”Visit “&A2)
…which can be copied down the list.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.