Yesterday, in the 30XL30D challenge, we replaced text with the SUBSTITUTE function, and used it to create flexible reports.
HYPERLINK Function
For day 28 in the challenge, we’ll examine the HYPERLINK function. Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.
So, let’s take a look at the HYPERLINK information and examples, and if you have other tips or examples, please share them in the comments.
Function 28: HYPERLINK
The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet.
How Could You Use HYPERLINK?
The HYPERLINK function can open a document, or jump to a specific location, so you can:
- Link to location in same file
- Link to Excel file in same folder
- Link to website
HYPERLINK Syntax
The HYPERLINK function has the following syntax:
- HYPERLINK(link_location,friendly_name)
- link_location is the text string for the location where you want to go.
- friendly_name is the text you want displayed in the cell
HYPERLINK Traps
If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel’s Ribbon.
That should show you the correct syntax, then recreate that in your link_location argument.
Example 1: Link to location in same file
There are several different ways to create the text string for the link_location argument.
In the first example, the ADDRESS function returns the address for row 1, column 1, on the sheet that is named in cell B3.
The pound sign (#) at the start of the address indicates that the location is within the current file.
=HYPERLINK(“#”&ADDRESS(1,1,,,B3),D3)
You could also use the & operator to construct the link location. Here, the sheet name is in cell B5 and the cell is in C5.
=HYPERLINK(“#”&”‘” & B5 & “‘!” & C5,D5)
For a link to a named range in the same workbook, just use the range name as the link location.
=HYPERLINK(“#”&D7,D7)
Example 2: Link to Excel file in same folder
To create a link to another Excel file, in the same folder, just use the file name as the link_location argument for the HYPERLINK function.
For files that are up a level or more in the hierarchy, use two periods and a backslash for each level.
=HYPERLINK(C3,D3)
Example 3: Link to a website
You can also link to website pages with the HYPERLINK function. In this example, the URL is constructed from text strings, and the website name is used as the friendly name.
=HYPERLINK(“http://www.” &B3 & “.com”,B3)
Download the HYPERLINK Function File
To see the formulas used in today’s examples, you can download the HYPERLINK function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the HYPERLINK Video
To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this short Excel video tutorial.
_____________
Crazy: Using excel 2003 (running on xp) internetexplorer needs to be your default-browser otherwise you get an obscure error-message
Dont know if this is a requirement on excel2007/2010 too
Okay, that # sign was my key missing piece. I couldn’t believe how hard it was to construct a hyperlink to jump within the spreadsheet. I used the following formula to build the filename part of the hyperlink:
=MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1)),FIND(“]”,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))+1)
It just seemed so ridiculous. Now I know it was!
Another useful feature of the HYPERLINK function is to send an email using the mailto protocol. For example,
HYPERLINK(“mailto:[email protected]”,”Email me”).
To pre-fill more fields in the email you can add those to the hyperlink as follows.
HYPERLINK(“mailto:[email protected]?subject=Sesame Street&body=Have you seen Cookie Monster?”,”Send E-Mail”)
See Chandoo’s great video tutorial for designing a birthday reminder template.
Extending Reuvain’s point re: use of HYPERLINK for generating email (useful where mail clients are unknown – ie VBA precluded)
There are obvious constraints re: attachments, auto send and 255 char limitation
Formatting can be achieved via HEX: http://www.pcguide.com/res/tablesASCII-c.html
=HYPERLINK(“mailto:[email protected]?subject=Test%20Email&body=Hello%0A%0ARegards,%0AMe”,”Generate E-Mail”)
On an aside it’s also worth being aware of the fact that a cell containing a link generated via Hyperlink function does not invoke the FollowHyperlink event in VBA.
This is the link to Chandoo’s video tutorial that I mentioned above.
http://chandoo.org/wp/2010/10/26/birthday-reminder-template/
[…] 30 Excel Functions in 30 Days: 28 – HYPERLINK […]