Create Searchable Drop Down List for Table With Multiple Rows

[MikeHarper2039]

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

[#2]

@MikeHarper2039, try exploring the file I attached here and apprise me of your thoughts thereon.

[#3]

@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

[#4]

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&"*"),)