How to make a SEARCHABLE drop down list in Excel

In this Excel training video, we’ll take you through the process of constructing a searchable drop-down list in Microsoft Excel. Unlike conventional Excel drop-downs, this specialized feature works like a dynamic search bar, enabling you to swiftly locate specific worksheets within your workbook.

Navigating through numerous tabs at the bottom of your Excel interface can be cumbersome, and our tutorial addresses this challenge by introducing a streamlined solution that allows you to search for and link directly to the desired worksheet.

By the end of the video, you’ll not only have a newfound proficiency in implementing this advanced Excel feature but also a valuable tool to enhance your productivity, saving you time and effort when working with large and complex workbooks.

Resources

Steps to enable Developer in Excel (Windows):

1. Go to “File” in the top left.
2. Head down and click “More” then “Options.”
3. Select “Customize Ribbon” in the left menu.
4. Head to the Tabs list on the right and scroll down to “Developer.”
5. Check the box next to “Developer.”
6. Then hit “OK.”

Steps to enable Developer in Excel (Mac):

1. Head to the top left of the screen and click “Excel.”
2. Select “Preferences” from the dropdown.
3. In Preferences, select “View.”
4. In View, head down and check the box next to “Developer.”
5. Finally, close the dialog box.

VBA code to grab all worksheets:

Sub Pages()
    Dim ws As Worksheet
    Dim wsList As Worksheet
    Dim i As Integer

    ' Create a new worksheet to list sheet names
    Set wsList = Worksheets.Add
    wsList.Name = "SheetList"
    
    ' Set the starting row in the new worksheet
    i = 1

    ' Loop through each worksheet and add its name to the list
    For Each ws In Worksheets

        wsList.Cells(i, 1).Value = ws.Name
        i = i + 1

    Next ws
End Sub

Formula to link search icon to worksheets:

=HYPERLINK("#'" & G2 & "'", "🔎")