Setting up Searchable Dropdown list (WITHOUT Filter function)


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.


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)

@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


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.



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:



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