How to Create Dynamic Hyperlink in Excel (3 Methods)

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.

Dataset - Dynamic Hyperlink Excel

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.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Dynamic Hyperlink

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.

Data Sheets from Dynamic Hyperlink Excel

The actor’s name list is in the Data worksheet. And the details in the Details worksheet.

Details Sheet for Dynamic Hyperlink Excel

Now, let’s create the dynamic hyperlink. There are several approaches, let’s explore those methods.

1. Hash (#) – Sheet Cell Reference to Dynamic Hyperlink

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

HYPERLINK with Hash to dynamic link

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.

Generate Dynamic Hyperlink

Click the link, it will take you to the destination cell.

Destination cell from dynamic hyperlink

Let’s use the AutoFill feature and generate the hyperlink for the rest of the values.

Autofill the dynamic hyperlink

But there is a problem, the cell references will not be updated automatically.

Dynamic Hyperlink cell not updated

We need to change the cell references manually.

Modify cell for hyperlink

Like for Leonardo DiCaprio, we have modified the cell reference to B9. This will now be in link with the correct cell.

2. Hyperlink Dynamically Using MATCH

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.

Actor and Link fields for hyperlink

Let’s create a drop-down list for easing the selection of actors.

Drop-down list - Dynamic Hyperlink Excel

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

HYPERLINK-MATCH Formula to generate dynamic one

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.

Dynamic Hyperlink

Let’s click the hyperlink. It will take you to the correct destination.

Destination cell from hyperlink

Let’s change the actor name and click the link again.

Change of value in actor field

You will find the destination has been changed, the hyperlink is working dynamically.

Hyperlink changes dynamically

3. Dynamic Hyperlink Using INDEX-MATCH

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.

We will use the MATCH and HYPERLINK functions and they will work the same as they do in the earlier section. In addition INDEX ad CELL functions will play a useful role.

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

HYPERLINK-CELL-INDEX-MATCH formula to create dynamic

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.

Dynamic Hyperlink 3

Click the hyperlink, it will take you to the destination cell.

Destination cell from hyperlink

Let’s change the actor name and then click the hyperlink.

Change of value in actor field

We are able to generate the dynamic hyperlink that’s why it will take us to the correct destination.

Hyperlink changes dynamically

Conclusion

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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo