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”)