Create a Searchable Drop Down List Just Like Google – Excel Trick



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.

Learn Excel VBA with our online course –

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;

Connect with us!
Google+ ►
Facebook ►
Twitter ►

Nguồn: https://marishka-moi.com/

Xem thêm bài viết: https://marishka-moi.com/category/cong-nghe

47 Replies to “Create a Searchable Drop Down List Just Like Google – Excel Trick”

  1. hi, this is very informative, however im experiencing an issue, whenever the combo box is clicked, it zooms in and makes a real mess of ActiveX Control, i cant seem to resolve this. im using Excel 2016

  2. Help please. I can't get this to work. The last part that creates the list that you copy into the named range resolves as #VALUE every time. I have checked the syntax and it looks okay and tried to find a solution on Office Help, but to no avail. What am I missing? Here is my formula … =Clients!$AY$50:INDEX(Clients!$AY$50:$AY$550,COUNTIF(Clients!$AY$50:$AY$550,"?*"))
    Thanks.

  3. 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?

  4. This was amazing. I am a work study that is working through 450 plus company names and their hyperlinks. How would I keep the hyperlink as it produces the list?

  5. Hello, I have a problem I have tried you formula but from the beginning it goes wrong after I made the dropdouwn list and made a reference to the cell above, now I bring the formula: search ($ G $ 1, B2) I get # N / A, what am I doing wrong?

  6. Can I copy Combobox and paste it on the same sheet?
    If I send the sheet to someone else, will it work without the other person do anything additional?

  7. One other issue I came across was when selecting a list item by clicking on it. Say you've typed in a key word and 4 results appear in the list and you click one, on click you're updating the ComboBox ListIndex (this is a number that corresponds with the selected item in your list). If you click the second one down, you've updated the ListIndex to 1 (the index starts at 0) but then also you're searchable list updates and is refined based on the new value you've clicked on. If the searchable list is now only showing that one name you clicked on, then the ComboBox should be showing that name however for me it shows blank as the ListIndex is 1 which is where that name used to be before the update. I think this solution resolves the issue:

    In the ComboBox Click event in VBA add in:
    ComboBox1.ListIndex = 0

    This sets the ListIndex to 0 in anticipation of the clicked name being moved to the top of the searchable list. I think this is working however my mind isn't currently 100% thanks to a nasty flu.

  8. 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"), "?*")

  9. Hello, I tried your formula but the first formula went wrong I received a different error message than on your video. can you help me to continue?

  10. Dear GG, thx for this comprehensive explanation ! I just have one issue if I implement this , the Dropdown Menu keeps popping up every if I change input in non-related cells ? Do you have more tricks in your magic case ? Thanks & Cheers , Johannes

  11. I tried to do this with data validation and it worked but when I leave the text box empty it doesn't show all of the items on the list

  12. How can you do this to scale? I mean will you have to create multiple searchable tables to have a unique formula for each cell affected by the comboBoxes?

  13. It's Good, but how to insert the data in the combo box, to fill a column, and what if somebody has multiple lists in a datasheet to fill. If you could help, you will be the best tutor ever, because nobody shares the real application part.

  14. Amazing video tutorial. Please keep adding more excel challenging videos like this as you’re professionally changing people lives on here. Thanks a lot!!

  15. that's handy, assuming you know what the sentence or name etc starts with. could it be made to find Alejandra Camino or others with the last name Camino were you to start by typing Camino?

  16. YES GOOD BUT YOU USE COMBO BOX AND COMBO BOX FILE WILL BE SAVED IN MACROS TRY TO MAKE WITHOUT COMBO BOX SEARCHABLE DROPDOWN LIST LIKE THIS

  17. first of all, thanks this is amazing, but i have a problem, when i type something in other sheet and press the arrows (or not always, its a bit random) the drop down list appears and its very annoyng, any solution?

  18. Is it possible to take a similar approach to searching for a unique identifier from a dropdown list and then display an entire row of information linked to your unique identifier, e.g. name, address, date of birth, marital status, shoe size etc?

  19. Anyone try this searchable list with part #s (I.E. 1234TV-34)?

    I followed the tutorial and it seems to work.

    But, is the next step to copy/paste the combo box onto my sheet I want combo search box to appear on and then link it to my data table column range?

    Thx

  20. I'm not sure if this will offend you, but… I LOVE YOU! 🤗🤗🤗🤗🤗 Seriously… where have you BEEN all my life?

    I have been looking for this information for YEARS! No one has been able to explain it like this to where I actually UNDERSTAND and can follow along and it actually WORKS! Thank you! THANK you! THANK YOU! 💖💖💖💖💖 I am now a subscriber AND I am already set up to watch every video! I just… wow… words cannot express how thankful I am! 💖💖💖💖💖💖💖💖💖💖💖💖💖💖💖

  21. At minute 21:33 when I press enter, I get #Value! not one of the names in Column D. I have checked my formula carefully and had a coworker look at as well. The formula is exact =$L$2:INDEX($L$2:$L$78,COUNTIF($L$2:$L$78,"?*")) obviously my data is in different columns and I only have 78 lines of data, not 88 but that shouldn't matter. Do you have any suggestion for me?

Leave a Reply

Your email address will not be published. Required fields are marked *