SOLVED

Setting up Searchable Dropdown list (WITHOUT Filter function)

Contributor

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.

6 Replies
best response confirmed by Davidm54 (Contributor)
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.

 

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

@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.

@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. 

 

 

 

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.
Another option:
You can try using a free Excel add-in.
Just google "Excel add-in called Search deList".