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

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

Step 1:

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

• 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 hit OK.
• We can see a drop-down list in cell A2 with three options to select.

Step 3:

In cell A3, write down the formula that uses the HYPERLINK function.

Step 4:

Let’s copy cells A1:A3 from the worksheet named January to other sheets named February and March. The drop-down list in cell A2 and the formula in cell A3 are available on 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.

• Click on the Go link in cell A3.

• We navigated to the sheet named February.

• Choose a sheet name from the drop–down list and hit the Go link to navigate to the selected sheet.

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

In this method, use 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: April, May, and June.

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.

• Copy the drop–down 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

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

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

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

Notes

In a large dataset, we can freeze the drop–down 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 April worksheet.
• Go to the View tab.
• 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.

Download Practice Workbook

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

Related Articles

• How to Hyperlink to Cell in Same Sheet in Excel
• Excel Hyperlink to Cell in Another Sheet with VLOOKUP
• How to Combine Text and Hyperlink in Excel Cell
• Excel Hyperlink to Another Sheet Based on Cell Value
• How to Edit Hyperlink in Excel
• How to Create Dynamic Hyperlink in Excel
• How to Copy Hyperlink in Excel
• How to Convert Text to Hyperlink in Excel
• How to Create Button to Link to Another Sheet in Excel

<< Go Back To Excel Hyperlink to Another Sheet | Hyperlink in Excel | Linking in Excel | Learn Excel