Feb 21 2021 07:45 PM
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.
Feb 21 2021 08:27 PM
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.
Sep 24 2021 01:29 AM
Sep 24 2021 02:03 AM
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.
Oct 04 2021 07:17 PM
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.
Jan 30 2022 01:47 PM
Mar 30 2022 05:59 PM
Nov 03 2022 09:33 AM
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
Feb 21 2021 08:27 PM
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.