Setting up Searchable Dropdown list (WITHOUT Filter function)

[Davidm54]

Hi All,

I'm trying to set up a searchable dropdown list. It'll effectively be for searching for clients by name. 

I am trying to avoid using the filter function as not everyone will access the files in 365/web version.

https://www.bluepecantraining.com/create-a-searchable-drop-down-in-excel/

I've used the formulas from the video/workbook linked above.

It's almost working. As best I can tell, the issue lies in the "Workings" column.

Here's the formula from his sheet. 

=IF(ISNUMBER(SEARCH(‘Drop Down List’!$A$2,[@Product])),MAX($B$1:B1)+1,0)

This spits out the occurrences of the letters typed in his form (so If I type d, it returns 1 for the first d, 2 for the second, 3 for the third, etc)

When I set up the same formula in my spreadsheet, it is only returning 1s. So it just counts 1 everytime the letter appears, not 1,2,3 etc.

That seems to break the rest of the formulas.

Any help greatly appreciated.

[#2 · Accepted solution]

@Davidm54 Well, for the sake of folks searching this. The formulas in the link work. The answer is to not mess up cell references. I had one column doubling up. 

Link in OP is worth saving if you want to do the searchable drop thing.

[#3]

Hi I am working on the same requirement, implemented all the formulas mentioned, everything was smooth till the end and all worked fine but at the end I faced a problem while creating a drop down list, i see a pop up message saying "The source currently evaluates to an error" and drop down list is not coming please suggest. the solution.
thanks a ton in advance for the suggestions

[#4]

@legitautomotive 

I suggest you open a new forum thread.

By the way, there are a lot of different solutions regarding it as well.

Recently I assisted an user with this solution

Take a look there.

[#5]

@Davidm54 

The sample file downloaded from the video is working without any problem with me. The very interesting thing is when I used the same formular within my own Excel file, it did not work properly.

The issue is at Named Reference Formula column. I have to changed it into following and drag it down:

=IFNA(INDEX(ProductList[Product],MATCH(ROW($D$1:D1),ProductList[Workings],0)),"")

Now here comes the more interesting part. If I replace my version of the above formula into the sample Excel file, it did not work!

Weird! But hope this would help you a bit. 

[#6]

Thank you. I do need to revisit this shortly, but also notice that a built in version is in beta. So am thinking I might wait for that.

[#7]

Another option:
You can try using a free Excel add-in.
Just google "Excel add-in called Search deList".

[#8]

Does anyone know how to make this work for multiple rows?

Basically so that you can have a searchable list of drop downs for an entire column of cells rathe than just one? I've tried versions using Sort and Filter but my client has an older version of Excel which doesn't support these functions!

Thanks