How to Create a Drop Down List Hyperlink to Another Sheet in Excel

In this article, we’ll show how to navigate from one sheet to another by creating a drop-down list hyperlink to another sheet in Excel. We’ll use the HYPERLINK function and a VBA code in two different methods described with examples.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Methods to Create a Drop Down List of Hyperlinks to Another Sheet

Let’s say we have a dataset that represents sale details for the 1st three months of the year 2020 in three different sheets. We renamed the worksheets according to the month names as January, February, and March. We want to create a dropdown list that holds the sheet names and then navigate to the sheet with the selection.

Excel Drop Down List Hyperlink to Another Sheet


1. Create a Drop Down List Hyperlink to Another Sheet Using Formula in Excel 

In this method, we’ll use the HYPERLINK function to get our job done. At first, we need to create a dropdown list with the sheet names.

Step 1:

  • Select a cell (A2 in the worksheet named January, in this example) on which we’ll create the drop-down list.

  • Then go to the Data tab of the Excel Ribbon.
  • Click on the Data Validation tab.

Step 2:

  • In the Data Validation window, select the Setting tab (By default selected).
  • In the Allow drop-down list, choose the List option.

  • Then type January, February, and March in the Source input box and finally hit OK.
  • As an output, we can see a drop-down list in cell A2 with three options to select.

Excel Drop Down List Hyperlink to Another Sheet

Step 3:

Now in cell A3, write down the following formula that uses the HYPERLINK function.

=HYPERLINK("#'" & A2 & "'!A1","Go")

Excel Drop Down List Hyperlink to Another Sheet

Formula Explanation:

The HYPERLINK function needs two arguments to operate. The syntax is-
=HYPERLINK(link_location,[friendly_name])
link_location – Neet to set the location that the link will take us to. In our formula

  • # (pound sign) is to define that the location is within the same workbook.
  • A2 part takes the worksheet name from the selection in cell A2.
  • !A1 part determines the cell location of the selected worksheet to navigate.

The location of the link in cell A3 from the above screenshot is,
#’January’!A1
It means, clicking on the link will take us to cell A1 on the worksheet named January.
[friendly_name] – Name of the link. We set the name as “Go” in our formula.

Step 4:

Let’s copy cells A1:A3 from the worksheet named January to other sheets named February and March. Now the drop-down list in cell A2 and the formula in cell A3 are available to all the sheets. If we want to go to sheet February from sheet January, we need to-

  • Select the February option from the drop-down list.

Excel Drop Down List Hyperlink to Another Sheet

  • Then click on the Go link in cell A3.

Excel Drop Down List Hyperlink to Another Sheet

  • We’ve successfully navigated to the sheet named February.

Excel Drop Down List Hyperlink to Another Sheet

  • Similarly, we can choose a sheet name from the dropdown list and hit the Go link to navigate to the selected sheet.

2. Run a VBA Code to Navigate to Another Sheet Using a Drop Down List of Hyperlink in Excel

In this method, we’re going to use a VBA code to find the selected sheet name in the dropdown list and then navigate to the sheet. To illustrate this, we’ve created three new worksheets named April, May, and June.

Excel Drop Down List Hyperlink to Another Sheet

Follow the steps below:

  • In cell A2 in the April worksheet, create a drop-down list with items April, May, and June following section 1.1.

Excel Drop Down List Hyperlink to Another Sheet

  • Copy the dropdown list in cell A2 to other sheets named May and June.
  • Click the right button on the sheet name and select the View Code.

  • Now copy and paste the following code into the visual code editor.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SheetRng As Range, SheetName As String, TargetSheet As Worksheet
    Set SheetRng = Range("A:A")
    If Intersect(Target, SheetRng) Is Nothing Then Exit Sub
    SheetName = Target.Value
    For Each TargetSheet In Sheets
        If TargetSheet.Name = SheetName Then
            TargetSheet.Activate
        End If
    Next TargetSheet
End Sub

Excel Drop Down List Hyperlink to Another Sheet

  • Copy and paste the same code in sheets named May and June.

Now we can select a sheet name in the drop-down list and the code running in the background will take us to the selected worksheet. Here, we are in the sheet named April. Let’s select May from the drop-down list in cell A2.

Excel Drop Down List Hyperlink to Another Sheet

We’ve successfully navigated to the worksheet named May automatically.

Excel Drop Down List Hyperlink to Another Sheet


Notes

In a large dataset, we can freeze the dropdown list to facilitate the navigation to another sheet without scrolling to the top of the worksheet. To do this-

  • Select the third row as we want to freeze the first two rows in the worksheet named April.
  • Go to the View tab.
  • Then click on the Freeze Panes drop-down list.
  • Choose the Freeze Panes option.

The above steps will freeze the first two rows in the worksheet.


Conclusion

Now, we know how to navigate from one sheet to another using a drop-down list hyperlink to another sheet with examples.  Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo