Excel allows you to deal with links. Cases can be like hyperlink to cell, find links, deal broken links, and many more. Today we are going to show you how to create a dynamic hyperlink in Excel. For this session, we are using Excel 2019, feel free to use yours.
First things first, let’s get to know about the dataset that is the base of our examples.
Here we have information regarding a few popular actors. Their name and their details are stored in two distinct tables or lists. Using this dataset we will create a dynamic hyperlink.
Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.
You are welcome to download the practice workbook from the link below.
Hearing the word “Hyperlink”, one function that may come to your mind is the HYPERLINK. We will use this function to generate a dynamic hyperlink.
But before that, to make the examples compatible with most of the real-life cases, let’s split the two lists into two different sheets.
The actor’s name list is in the Data worksheet. And the details in the Details worksheet.
Now, let’s create the dynamic hyperlink. There are several approaches, let’s explore those methods.
To create a dynamic hyperlink using the HYPERLINK function we can insert the sheet name and the cell reference followed by a hash symbol (
#). So, the generic version of the formula will be
=HYPERLINK(“#”&”Sheet_name”&”Cell Reference”,”name representation”)
The use of hash is a pivotal one since it allows the link to be a working one in case of changing the sheet name or cell reference.
Let’s write the formula for our example
=HYPERLINK("#"&"Details!"&"B5","Click to See Details")
Here the sheet name is Details. We have written the name followed by a “
!”. Excel differentiates the sheet name and cell reference through “
And then the cell reference. This will generate the dynamic hyperlink.
Click the link, it will take you to the destination cell.
Let’s use the AutoFill feature and generate the hyperlink for the rest of the values.
But there is a problem, the cell references will not be updated automatically.
We need to change the cell references manually.
Like for Leonardo DiCaprio, we have modified the cell reference to B9. This will now be in link with the correct cell.
You may not be convinced about the dynamicity of the hyperlink we have generated in the previous section, since we need to modify the references every time manually.
Hope we can overcome that issue in this section. We will choose the actor and depending on our selection the hyperlink will be modifying automatically.
Let’s create a drop-down list for easing the selection of actors.
Now we will generate the dynamic hyperlink, collaborating HYPERLINK and MATCH. The MATCH function returns the position of a lookup value in a range.
Let’s explore the formula first
=HYPERLINK("#"&"Details!B"&(MATCH(D4,Details!$B$5:$B$9,0)+4),"Click to See Details")
Within the MATCH function, we have provided the selected actor name as lookup_value. And it returns the position from the Details sheet.
In the Details sheet, the information is stored between the B5:B9 range, that’s why we have added 4 to the result of MATCH.
This will create the hyperlink.
Let’s click the hyperlink. It will take you to the correct destination.
Let’s change the actor name and click the link again.
You will find the destination has been changed, the hyperlink is working dynamically.
In the earlier section, we have used the MATCH function alongside the HYPERLINK function. There we have added 4 to the result of MATCH. Depending on the cell position we need to change this value (4).
Changing the value manually from time to time may not be an ideal one. So, let’s form a formula to reduce this problem.
INDEX returns the value at a given location in a range and CELL returns information about a cell in a worksheet.
=HYPERLINK("#"&CELL("address",INDEX(Details!B5:B9,MATCH(D4,Details!B5:B9,0))),"Click to See Details")
The MATCH function returns the position when it finds the match and then INDEX traverses to that cell to retrieve the value. But here the CELL function plays its part.
You can see we have set “address” as the info_type of CELL. So, rather than fetching the value, it will get the cell reference itself.
Click the hyperlink, it will take you to the destination cell.
Let’s change the actor name and then click the hyperlink.
We are able to generate the dynamic hyperlink that’s why it will take us to the correct destination.
That’s all for the session. We have listed approaches to create a dynamic hyperlink in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we might have missed here.