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 & "'", "🔎")
Need help? This is a free service for Business Tech Planet readers. Pop in your problem below and we'll be in touch soon!

John Gleave

John Gleave has been a researcher, content writer, and senior editor at Business Tech Planet since 2022. John was formerly a data analyst and web designer with expertise in several programming languages, such as JavaScript, JQuery, PHP, CSS, SQL, and more! With a passion for writing and technology, he has now focused his skills on crafting tech guides for BTP. You can connect with John on LinkedIn.

Recent Posts