Home » Create a Hyperlink Drop-Down in Excel

Create a Hyperlink Drop-Down in Excel

In this tutorial, I am going to demonstrate how to create a drop-down menu that will hyperlink to other worksheets within a workbook.  Download the featured file here.

  1. The first step is to create a new worksheet in your workbook.  In this worksheet, enter the names of the worksheets that you want to be able to link to.  Enter each sheet name in a separate cell in a contiguous list.
  2. The next step is to create a new worksheet to contain your drop-down menu.
  3. In this new worksheet, select the cell where you want the drop-down to be. Then click on the Ribbon’s Data tab, click on the Data Validation button and the Data Validation dialog will open.
  4. In the Data Validation dialog, in the Allow: list select List.
  5. Click in the Source: box and then select the cells that contain the sheet names that you entered on a separate worksheet in step 1.
  6. Click on OK to confirm.  You will now have a drop-down list of worksheet names, but they won’t yet act as hyperlinks.
  7. In an empty cell in the same worksheet, type the following formula: =ADDRESS(1,1,,,B1) where the cell reference B1 is the cell containing your drop-down.  We will use this formula later.
  8. Next, we need to create a named reference. On the Ribbon’s Formula tab, click the Name Manager button and then click New.
  9. In the New Name dialog, type sheet_link in the Name box.
  10. In the Refers to: box select any empty cell in your worksheet.
  11. Click on OK to confirm.
  12. Next, select the cell that contains your drop-down and use the shortcut CTRL K to open the Insert Hyperlink dialog.
  13. In the Insert Hyperlink dialog, under Defined Names, select sheet_link.
  14. Click on OK to confirm.
  15. Now copy the formula you created in step 7.  You will use this formula shortly, in step 17.
  16. Open the Name Manager, select sheet_name and then click Edit.
  17. Paste the formula you copied in step 15 into the Refers to: box.
  18. Put the ADDRESS formula inside the INDIRECT function, like this: =INDIRECT(ADDRESS(1,1,,,B1))
  19. Click on OK to confirm.  Your hyperlinks should now work.