Create a Searchable Drop Down List Just Like Google - Excel Trick
HTML-код
- Опубликовано: 9 апр 2017
- Create a searchable drop down list in Excel just like Google. This trick is fantastic for large lists. Giving the functionality to search within a list goes beyond a standard drop down list.
I have a newer version of a searchable drop down list which is much easier here - • Searchable Drop-Down L...
Master Excel today with this comprehensive course - bit.ly/UltimateExcel
This video tutorial will show you how to create a combo box control on a spreadsheet and then use formulas to create a dynamic searchable drop down list.
This list would look awesome on your Excel dashboards and reports.
The video covers multiple Excel formulas and functions including INDEX, COUNTIFS and ROWS. It then uses a simple line of VBA code for the drop down combo box.
Subscribe to this channel to see more awesome Excel tips.
Find more great free tutorials at;
www.computergaga.com
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
You can now create a searchable drop-down list much easier with the FILTER function. Check out the new and improved video - ruclips.net/video/Ea_ACp5W8zI/видео.html
Great work sir, thanks for the effort :)
I dont see the filter function in my o365 :(
@@abunasar786 same problem, seem like only in Insider Fast update channel
I don't have to new office 365. I'm working on excel 2016. And this video helped a lot. But i am having one issue. That combo box we created keeps popping up
Great tip! The detail with VBA line was fantastic. Thank you!
This is ridiculously useful, thank you!
I found the this tutorial extremely helpful thank you! I did have some trouble with the dynamic range for the dropdown list as it would randomly repeat items and have empty spaces. I resolved this by just having the full range available, the empty spaces below were no trouble and it had the added benefit of the full list being available when the combobox is empty!
Cheers.
Hi Alan.. another great trick and video. For practice, I grabbed a list of US presidents from the Internet and used that as my list of names. Your instructions and formulas were clear and easy to follow. When competed, I added ComboBox1.Value = "" to the double click event of the combo box. So, I can double click the drop down box field to clear my previous entry and start over with a new search. I investigated the possibility of having the scroll wheel of the mouse scroll through the drop down list. I guess there are some complicated ways of getting that to work.. probably not worth the trouble. Thanks again for this great instruction and tutorial. I'll definitely add this to my bag of tricks. Thumbs up!
A pure sweat saver. Just Excellent. You just gifted me "Peace" in my work by this tutorial
Thank you Asim.
This is what I am searching for a long time and Thank you very much for your excellent support. ThanQ
You're welcome, thank you.
Amazing video tutorial. Please keep adding more excel challenging videos like this as you’re professionally changing people lives on here. Thanks a lot!!
Thank you very much Aime. Your comments are much appreciated.
BRILLIANT! This is just what I needed. Thank you so much!
You're welcome. Thanks Joseph.
Extremely useful and absolutely amazing. Many many thanks Computergaga, you've just saved a life!
You're welcome Gbashi, thank you.
A few years on since this video, still extremely useful! Thanks for this! I've always been looking for something like this with minimal VBA, if not none, as possible.
You're very welcome!
I found this incredibly informative. Really great work!
Thank you Russ. Much appreciated.
This is brilliant, picked up a lot of handy tips and tricks. There's a couple of comments mentioning the issue where the list is dynamically updating in the combobox, however the list rows are not. It shows the number of list rows based on the count at the time the name range is defined (in your video it's 9). Something that seems to be functioning for me: In VBA where the ComboBox1.DropDown was added when the combobox changes, add in an additional line to dynamically update the ListFillRange also. For your spreadsheet the code would be:
ComboBox1.ListFillRange = "D2:D" & Application.CountIf(Worksheets("Searchable Drop Down").Range("D2:D88"), "?*")
Masterpiece! Thanks for the share!
Your welcome Huzefa, thank you.
Wonderful video. Thanks so much for sharing!! This is incredibly helpful for my application. You did a great job walking the user through the setup process step by step.
Thank you Kevin.
Wow what a great video. I had a little trouble understanding the countif function use on the returned list of names to produce the dynamic range. So what i did was use the count function on the column that returned the numbers. It also works.
Absolutely. Good work Joe.
Great tutorial! Very informative and easy to follow. I now know how to repeat this as well as explain the steps taken to get here. Thanks!!
Thank you Brian.
wow.brilliant. beautiful video. worth to watch every second of this video. GBU.. thanks for sharing
You're welcome Haider.
*Brilliantly explained.I have learnt a lot from this youtube tutorial, thank you.*
Your welcome, thanks Ivor.
It has taken me about 3 days to finally figure this out! But it is amazing. Fantastic Job!
Thank you Joshua.
SUPER well done, my man. great video.
Thank you Andrew.
Very effective and effortless to the final users of an Excel File! Thank you!
Your welcome Edardo, thank you.
Amazing tutorial, thank you so much for sharing your knowledge.
You're very welcome Hugo.
Wonderful ! I can't wait to use that. Thx mate !
My pleasure.
Thanks a lot, I did follow your instructions in the web you have given also use some other tutorials when I get struck, now it's working perfectly. Thanks again for your great support extended.
Fantastic to hear. You are more than welcome Cyril.
Wow that's a very nice result. I'm gonna have to try that. I think I'd like to make one that works for a whole column.
Very cool trick Mr. Computergaga! You rock man.
Thank you Robert.
You explained it in a super easy way! I followed it step by step and got the same result. Thanks a lot. It is so fun to work with Excel.
You're welcome Manjunatha 👍
looking for this type of search from very long time, very good trick and work
Thank you.
Awesome! very useful and understandable, thanks mate!
You're welcome tetleyplus.
Brilliant! Exactly what I needed.
Happy to be able to help Harry.
Very and clear and useful, Thank you sir!
Thank you Julius.
Dear Sir,
it's simply fantastic ....and very worth to work with, your help in this regards is priceless ..love you so much.
Thank you Siva.
Just what i was looking for. Great
Excellent! Happy to help.
You are a gem.. Wonderful explanation .. Thanks for this video.. Subscribed right away..
Thank you Sumant.
Brilliant! A thousand thanks!
You're welcome Chris.
Execellent class, I was able to execute every single step and make a wonderful and very intuitive Dashboard for my boss.
Great! Nice work.
Awesome tutorial, thank you so much for sharing....
My pleasure Vijay.
Thanks a lot for the important formula explained in detailed format.
You're welcome, Rajesh.
impeccable
A big thank you to a great professional
Thank you very much Abu.
Wow! That was an awesome tutorial.
Thank you.
Excellent presentation. Thanks
You're welcome Kunhimoidu.
Amazing video!! Thank you 🙏
You're welcome. Thank you very much.
Thank you so much for the excellent video, I love it. I followed your instructions and my ComboBox works perfect; however, it pops up on other sheets within the workbook. I saw here in this page that some others have the same issue. Do you possibly have any solution for that?
Awesome tutorial....thank you very much....
Welcome 😊 Thank you, Vivek.
Extremely helpful and excellent. Thanks.
Great! Thank you, Ahmed.
Great informative video thanks!
You're welcome Craig, thank you.
thank you so much for this crystal clear lesson
You're welcome Two Stones.
This is just awesome, and awesomely useful, speechless, thank you.
Glad you liked it, Soumya.
great job ,thank you
You're welcome Fatema, thank you.
thanks for the video!
My pleasure Adriana.
That was fantastic. Thank you. One small doubt , in case you want to reference the combo box in a formula and pull out some data in the sheet, say based on the value in the combobox , vlookup another column or something , what would be the cell reference i should give - G1?
Excellent, thank you very much
Your welcome.
Very well explained sir. Thank you so much. Really helped a lot.
Great to hear. Thank you Balakrishnan.
Thank you sir. Perfectly presented. Extremely helpful.
You're very welcome! Thank you.
It was so much informative and easy to understand. Thank you so much sir :-)
You're very welcome Rakesh.
excellent video..very useful
Your welcome Manish
Great video! Is there a way to create multiple drop down search boxes in one excel workbook page that are independent from each other but still pull from the same lists?
Most beneficial tutorial. I like it very much.
Great to hear, thank you.
Thanks for such great explanation
You're very welcome.
Very good tutorial, thank you.
You're welcome. Thank you.
very useful, Thank you.
You're very welcome Thusitha.
Thanks for sharing this!!! This is how to explain the excel...
Your welcome, thanks Zaman.
Another high-quality video. You are a champ.
Thank you, Ahmed 😊
I don't have the Filter function so I used this! Another great Video!!
Thank you Maximillian. If you have 365, you can convert to the monthly channel to get FILTER - bit.ly/2G8qHhx
Wow this was amazing
Thank you Rajesh. Glad you enjoyed it.
Thanks so much for sharing , awesome and useful.
You're welcome. Thank you very much.
One of the best tutorials... thank you
You're very welcome!
Thank you so much sir....simple and easy way to understand hats off, i have designed some project using this video , it almost worked but at the end ,few multiple fields cannot be seen in drop down list... i'm using excel 2010 version...Thanks in advance
Thank you... this is so well explained. :)
Thank you very much 😊
Thanks a lot and that's really useful.
Your welcome Mohamed.
Excellent vidéo, Thanks ... Thanks.
You're very welcome.
Brilliant!!!
Thank you.
Nice tutorial! Helped alot! How can i do to all lines in a column be searchable with combobox ?
Thank a lot for this usefull video this is actually best explanation about the searchable dropdownlist for excel, but i have a question if i have a duplicated results on my searhed dropdownlist what sould ı do?
Very very helpful. Thank you so much. God bless for sharing your gift to the world.
Thank you 😊
Hi, this is very helpful! Is there any way to prevent the users text to remain in the combo box if it does not match anything in the list?
Very clear explanation.👍
Thank you Jonathan.
great clip I have watched and I can try to do follow you.Thank you ^^
My pleasure Tina.
Exelente formulacion me ha funcionado al primer intento, solo tengo una pregunta cuando hago una copia del libro la lista ya no me despliega hacia abajo y no se como resolverlo.
Excellent video
Thank you, Aston.
This is definitely awesome!
One additional question, not sure if anyones asked...how could i use this but include multiple drop downs using the same list of data?
thank you so much!!!
Your welcome Paolo.
Top Job Thx
Thank you Steve.
Excellent, thank you very much. What needs to change if I were to search and add multiple values in individual cells down. For example if you have 87 records in the list and I want to search 10 unique values 10 times and add them down in rows individually instead of only one value as you showed in the video.
Great piece of info explained thanks.
How would this work if my customers name list is on one sheet & the code on another ?
I mean if i add 3 new customers to Column A how will the code on the other sheet update to allow these 3 new names ?
Thanks
Great video! Amazingly explained and especially liked the usage of Index, Match,Countif and Search functions. One challenge though which i am facing is that the length of the drop down box on my excel only shows four values max. Rest all is working but i dont know what i am missing. Thanks!
Yo May need to use the offset formula and count the lines down with "what the maximum number in column C" that will fix you up
So go to formulas - name manager and do your changes
It Should be like that according to this video you need to click the cell to input the address correctly
=offset($D$2,,,max(C2:C88))
Thanks much! Super duper useful.. ☺️
Happy to hear that! 👍
WOW, Sir, I have learned two new things in this video, that is "Perfect Searchable Dropdown List" & "Using a formula instead of "OFFSET" function". brilliant sir.
Great! Thank you.
One of the best ...Thx alot
You're very welcome Nils.
thanks ...its very usefull
Great! My pleasure 👍
Great !!
Thank you Enamul.
thank u sir, i just so much excited to watch it NXT video. u r great.. I got my solution by watching Ur video.. thank u..
Your welcome Jyotiranjan. Thanks for your comments.
thank u very much sir
You're welcome Vazir.
thanks a lot. it helped me very much
Happy to help Marcellin.
u r a extraordinary person
and master mind
Thank you.
This is a really great function, and it was just the function I was on the lookout for my VBA project, and I got olmost all of it to work, but for some reason I can olny click on the first searchresult in the combobox, for the combobox to write the value to the specified cell, all others return a empty value.
I am pretty sure it has to do with the ComboBox in VBA, but is there anyway to get the combobox to return the value i clicked on?
Wonderful!! Thank you so Much!!
I tried to extend my range if i needed to add new data but it doesn't work
Okay solved :) i forgot to update the formula in the named range under name manager
Excellent work Zayd.
Excellent, simplest way I've seen
Super! My pleasure.
Hey Computergaga
Great video, followed exactly as you described and it worked brilliantly for me. I want to create 3 of these combo boxes on a sheet (sheet 1) and have used separate sheets (sheets 2, e & 4) for the reference of each list as you demonstrate here. The problem I have is that every time I enter text in one of the comboboxes, all the boxes then show the same text! Each combo box has a different name and I even thought using a different linked cell might help but no😕. Help, please....
Thanks for all your videos, really clear tutorials and paced well. I always watch your videos over anyone else 👍🏻
Thank you
You're welcome!