In this tutorial, I am going to demonstrate how to create a drop-down menu that will hyperlink to other worksheets within a workbook.
Click here to download the featured file.
- 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.
- The next step is to create a new worksheet to contain your drop-down menu.
- 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.
- In the Data Validation dialog, in the Allow: list select List.
- 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.
- Click on OK to confirm. You will now have a drop-down list of worksheet names, but they won’t yet act as hyperlinks.
- 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.
- Next, we need to create a named reference. On the Ribbon’s Formula tab, click the Name Manager button and then click New.
- In the New Name dialog, type sheet_link in the Name box.
- In the Refers to: box select any empty cell in your worksheet.
- Click on OK to confirm.
- Next, select the cell that contains your drop-down and use the shortcut CTRL K to open the Insert Hyperlink dialog.
- In the Insert Hyperlink dialog, under Defined Names, select sheet_link.
- Click on OK to confirm.
- Now copy the formula you created in step 7. You will use this formula shortly, in step 17.
- Open the Name Manager, select sheet_name and then click Edit.
- Paste the formula you copied in step 15 into the Refers to: box.
- Put the ADDRESS formula inside the INDIRECT function, like this: =INDIRECT(ADDRESS(1,1,,,B1))
- Click on OK to confirm. Your hyperlinks should now work.