Excel Hyperlink Function to link to other cell, open website, open document

What does Excel Hyperlink Function do?

Excel Hyperlink Function is to create  a hyperlink in a Cell. The hyperlink can be linked to external website or a Cell in a worksheet.

In Excel VBA, there is a similar Method called FollowHyperlink, but it actually “go to” hyperlink instead of creating a link for user to click on.

Syntax of Excel Hyperlink Function

HYPERLINK(link_location, [friendly_name])

link_location The path of the file you want to link to.
To link website, type “http://yourwebsite”
To link to a Cell in another worksheet, type “#Sheet1!A1”
To link external file, type “C:\..\MyFile.xlsx”
friendly_name Optional. The displayed text for the hyperlink

Example of Excel Hyperlink Function

The below example was answered by me in Microsoft Community, it shows how to use Hyperlink Function with Match Function.

Question

Sheet 1 Contains my class register and their contact details for an individual class

Sheet 2 contains a full database of past and present students

What I would like is a way to indicate on sheet 1 which row number this students info is located on the full database which is sheet 2 (please note that students change every half term)

The way my current work book works is on sheet 1 I have a set code in column B for each place available in my class.  I have made a formula (using index and match) to insert the students info on to sheet 1 from sheet 2 when the corresponding code is entered into a cell in Column B on sheet.

What I need now is a way to either add a link on sheet 1 column S to jump straight to the students info on sheet 2 OR a formula to tell me which row the info will be located on to save me scrolling through sheet 2 or using the ‘find’ option

Answer

You can use MATCH Function to find the row number of lookup value in sheet 2. Type the following formula in Sheet 1, assume that you look for A2 value in Sheet2!$A$1:$A$6

=MATCH(A2,Sheet2!$A$1:$A$6,0)

You can then create a hyperlink making use of the above result. Assume that you want to create hyperlink and jump to the column B of the lookup value

=HYPERLINK(“#Sheet2!B”&MATCH(A2,Sheet2!$A$1:$A$6,0),”hyperlink name”)

Outbound References

https://support.office.com/en-us/article/HYPERLINK-function-d1a84847-ceaf-4e8b-b583-55787a69b125?ui=en-US&rs=en-US&ad=US

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *