Forum Discussion

MikeHarper2039's avatar
MikeHarper2039
Copper Contributor
Aug 25, 2020

Create Searchable Drop Down List for Table With Multiple Rows

Hi there, Tech Community newbie here so I hope I manage to explain what I'm trying to do accurately (and also hope that someone can point me in the right direction!).

 

I used Leila Gharani (a great resource for the occasional Excel formula needy!) formula to create a searchable dropdown list - worked great but only for a single row.  

 

I would like to use the searchable dropdown in a 100-row spreadsheet to restrict the ability of users to enter names that are not on a master data set - is there a way to do this whilst keeping the neat searchable function or do I just have to live with the whole validation list as it would appear without the search functionality?

 

Cheers

 

Mike

  • kadirguler's avatar
    kadirguler
    Copper Contributor
    Jan 22, 2021

    MikeHarper2039  I recommend that you review the searchable drop-down lists example I created on an invoice template. I prepared in range(A17:A33) the drop-down lists to bring data from the Products sheet to the invoice sheet. Thousands of data can be searched in the drop-down list and the result is added to the cell.

     

     

    Source,sample workbook here : Excel searchable drop down lists

     

     

    • nicoleahmed's avatar
      nicoleahmed
      Brass Contributor
      Jan 10, 2023
      This is brilliant thank you! I didn't use the VBA code to sort a-z, i just used the table sort function.

      My range formula: AllSites: =OFFSET(AllSites!$B$2,,,COUNTA(AllSites!$B:$B)-1)
      My data validation formula: =OFFSET(AllSites!$B$2,MATCH($B9&"*",AllSites,0)-1,,COUNTIF(AllSites,$B9&"*"),)

Resources