Searchable Drop-Down List || Without FILTER Function of MS365

#With_Practice_Sheet
Normal Filter in Excel is fine but if your sheet is equipped with a searchable drop down list it becomes a real luxury in selecting your data from the drop down in case your list is lengthy.
The google drive link for the the practice worksheet:-
drive.google.c...

[@emzeemendoza]

what if there are multiple search box, example you are using excel for invoice making and you want to have searchable dropdown list on the description column?

[@suhaibhassan9607]

I am using excel via Mac. Whenever I open "Data Validation" and select list and press F3 it doesn't work. The dialogue box "paste name" doesn't appear. Any solution?

[@offtooffice8742]

Try Control and F3 together or else directly type the defined name. However Control F3 should work. Do let me know.

[@rmjaln]

Would you post the formula using aggregate instead of small so you don’t need CSE？

[@David-tj8vz]

Hi What if you have multiple dropdown Cells

[@offtooffice8742]

Do u mean you want to have the drop down list to cells below ? There is a short video to overcome this problem
ruclips.net/video/Gn9pwgpsweM/видео.html

[@ukmshorts]

Good 👍👍

[@ketank900]

👍

It should be with autocmplete function,

[@mirrrvelll5164]

Amazing video!

[@offtooffice8742]

Glad u liked it.

[@manjuk5779]

How can we add new item to the source data?

[@syedhabeeb9423]

Very nice video.
Hi, here you have used search drop-down for only one cell D7. I want same search drop-down for a whole column cells. Is it possible?

[@offtooffice8742]

Yeah it is possible. I won't say exactly but it's a kind of workaround. I would let u know tomorrow. preparing altogether a video for it as others too have queried about it.

[@syedhabeeb9423]

@@offtooffice8742 Thank you

[@offtooffice8742]

Here is Your solution.
ruclips.net/video/Gn9pwgpsweM/видео.html

[@syedhabeeb9423]

@@offtooffice8742 Amazing, thank you so much for such a quick response. This is what I needed.

[@Michael-fl9lh]

@@offtooffice8742 But that video is for "Date Functions"...

[@sessium6e]

hi, i'm using your spreadsheet combine with my data, but it''s suddenly not working,
what i change is just the data range/array... can you help me? thank you
ps: i'm using excel 2019

[@offtooffice8742]

I will come back to you shortly. If u r okay with sending the file u can send it to [e-mail]. or alternatively u can send me the screenshot of the relevant portion. Meanwhile I will have to go through the formula used by me in my sheet.

[@sessium6e]

@@offtooffice8742 thank you for your reply, i'll send the modified file to you...

[@jasonmanttan1756]

Is there a way to remove what was typed into the searchable dropdown list every time the dropdown arrow is clicked?...that way it'll function better and like a normal dropdown list.

[@offtooffice8742]

Normal drop down list is the one explained first and of course that was not searchable. The one created later is searchable which filters the relevant data only. Aa regards to its visibility,especially from aesthetic point of view, u can customise the formatting which will change the way the data there is displayed. So say if I have keyed in p and still I want to hide p. U simply go to the cell formatting of the cell where we enter keywords by pressing control 1. set a format as under
General;general; general;
This way u can hide the display of p or k in that cell.
The task u want to achieve can exactpy be obtained by writing an even subroutine which is initiated the moment u select that cell and the monent is is selected u delete thae content of the cell. It will be one liner code
Sub
Range("d5").Delete
End

[@DAR_animationvideos]

hi! ctrl shift enter is not working on my laptop

[@offtooffice8742]

Which version of excel are u using? May be u r in MS 365 which is natively programmed to handle arrays not requiring control shift enter.

[@gururajpatil4158]

hi, i want to make this dropdown list can u help me will mail u details sheet.

[@offtooffice8742]

Mail to [e-mail] ur sheet with requirement