Microsoft 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 3 quick ways how to create a dynamic hyperlink in Excel. For this session, we are using Office 365, feel free to use yours.
How to Create Dynamic Hyperlink in Excel: 3 Suitable Ways
In order to create a dynamic hyperlink in Excel, we can use the different functions. We will discuss that in the later part. Let’s get to know about the dataset first which 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 larger and more complex dataset.
To make the examples compatible with real-life cases, let’s split the two lists into two different sheets. The actor’s name list is in the Dataset worksheet.
And the details in the Details worksheet.
Now, let’s create the dynamic hyperlink. There are several approaches, let’s explore those methods.
Read More: How to Create a Hyperlink in Excel
1. Use HYPERLINK Function to Create Dynamic Hyperlink
To create a dynamic hyperlink, we can use the HYPERLINK Function.
Steps:
- Select a cell first to create a dynamic hyperlink.
- Next, input the following formula in that cell to create a dynamic hyperlink.
=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.
- Change the cell references manually.
- Like for Leonardo DiCaprio, we have modified the cell reference to C9. This will now be in link with the correct cell.
Read More: How to Link a Website to an Excel Sheet
2. Apply MATCH Function to Create Dynamic Hyperlink
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 where we are going to use the MATCH function to create a dynamic hyperlink. Based on our dataset, we will choose the actor and the hyperlink will be modified depending on our selection automatically.
Steps:
- Create a drop-down list for easing the selection of actors. For this, select a cell first to define the location of the drop-down list.
- Next, go to the Data tab.
- Pick Data Validation from the Data Tools tab.
- A Data Validation wizard will appear. Go to the Setting tab.
- Select List in the Allow section and define the range in the Source section.
- Followingly, click on OK to finish the drop-down creation process.
- Now, we can see the drop-down with the selected data.
- Now, input the following formula to create a dynamic hyperlink.
=HYPERLINK("#"&"Details!B"&(MATCH(B5,Details!$B$5:$B$9,0)+4),"Click to See Details") 
- Finally, press the ENTER button to have a dynamic hyperlink. Click on the hyperlink which will take you to the correct destination.
Read More: How to Activate Multiple Hyperlinks in Excel
3. Combine INDEX and MATCH Functions to Create Dynamic Hyperlink
In the earlier section, we have used the MATCH function alongside the HYPERLINK function. We can use the MATCH and HYPERLINK functions and they will work the same as they do in the earlier section. In addition INDEX and CELL functions will play a useful role.
Steps:
- Generate a drop-down list first.
- Now, input the following formula in a cell where you want to generate a dynamic hyperlink.
=HYPERLINK("#"&CELL("address",INDEX(Details!B5:B9,MATCH(B5,Details!B5:B9,0))),"Click to See Details") 
- As the ending step, hit the ENTER button to have a dynamic hyperlink. Afterward, click on the hyperlink which will take you to the defined destination.
Read More: How to Edit Hyperlink in Excel
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
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.
Further Readings
- How to Hyperlink to Cell in Same Sheet in Excel
- Excel Hyperlink to Cell in Another Sheet with VLOOKUP
- How to Combine Text and Hyperlink in Excel Cell
- How to Add Hyperlink to Another Sheet in Excel
- Excel Hyperlink to Another Sheet Based on Cell Value
- How to Create a Drop Down List Hyperlink to Another Sheet in Excel
- How to Copy Hyperlink in Excel
- How to Convert Text to Hyperlink in Excel
- How to Create Button to Link to Another Sheet in Excel
<< Go Back To Hyperlink in Excel | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Dear Shakil Ahmed,
nicely mentioned, can we automate the sheet name based on a cell value? what I mean to say that as you mentioned above the cell value linked to details sheet but what would be the way to link the individual sheets based on cell value?
Hi Samad, thank you for reaching out. Fortunately there is a way to automate sheet name based on a cell value. In the image, you can see that I have a cell value in A1. I also have values in A1 cell of the other sheets. I will change the name of the corresponding sheets based on the value in A1.
Now, type the following code in a VBA module and run it.
You can see that the name of the sheet changes according to the cell value of A1. If you store sheet names in another cell such as B5, you need put this cell reference instead of A1 in the VBA code. Otherwise you will encounter errors.
So useful and clearly explained! Thanks!
Hello Ani,
You are most welcome.
Regards
ExcelDemy