Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Dynamic Hyperlink in Excel (3 Quick Ways)

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.


3 Suitable Ways to Create Dynamic Hyperlink in Excel

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.

Dynamic Hyperlink Excel

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.

Dynamic Hyperlink Excel

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


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

Use HYPERLINK Function to Create Dynamic Hyperlink

  • 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.

Use HYPERLINK Function to Create Dynamic Hyperlink

  • 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.

Use HYPERLINK Function to Create Dynamic Hyperlink

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


Similar Readings:


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.

Apply MATCH Function to Create Dynamic Hyperlink

  • 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.

Apply MATCH Function to Create Dynamic Hyperlink

  • 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.


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.

Combine INDEX and MATCH Functions to Create Dynamic Hyperlink

  • 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.

Combine INDEX and MATCH Functions to Create Dynamic Hyperlink


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

Shakil Ahmed

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.

2 Comments
  1. 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.

      Sub ChangeSheetName()
      Dim mn_Worksheet As Worksheet
      For Each mn_Worksheet In Sheets
      mn_Worksheet.Name = mn_Worksheet.Range("A1")
      Next mn_Worksheet
      End Sub

      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.

Leave a reply

ExcelDemy
Logo