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