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.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
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.
To create a dynamic hyperlink, we can use the HYPERLINK Function.
- 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.
- Excel Hyperlink to Another Sheet Based on Cell Value
- How to Link a Table in Excel to Another Sheet (2 Easy Ways)
- How to Add Hyperlink to Another Sheet in Excel (2 Easy Ways)
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.
- 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.
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.
- 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.
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.