As might know there is no autocomplete functionality or any other features such as different font sizes with good old Data Validation cell dropdowns.
On a client’s project I worked on today we really needed autocomplete capability. The dropdown list was 80 rows long and the values were messy to scroll through some with 3 syllables. But I would need 80 comboboxes so for me just not practical, they are tedious to maintain and slow to display. Was there some magic VBA out there to come to the rescue maybe?
Yes there was! 
I came across Debra Dalgleish’s brilliant solution in her fantastic Contextures site here Data Validation Combo Box using Named Ranges and there is a link for downloading the workbook example. This is such a wonderful solution! If there was a contest for the best Excel workaround ever, this would get vote every year.
Basically using the VBA Worksheet_BeforeDoubleClick event each dropdown when double clicked, morphs from a basic cell with data validation to a Control X combobox with the autocomplete functionality, that cells data validation range, the number of rows you want to see and mercifully larger font until you exit the combobox when it then goes back into hiding. What an awesome idea! I am not going o repeat more of here because it’s Debra’s amazing work and all there on her site.
But the code didn’t work for me at first. After a half hour of frustration as to why, the penny dropped. My named ranges for the data validation lists were created using structured references as the data was within tables, but the combobox control only works with simple range references. I set up the named range as the data was within a Table and I wanted to take advantage of the dynamic nature of Tables ie add a row to the Table automatically adds to the range name reference.
So to use this solution, I had to think ‘old school’ and change my named range reference back to a dynamic range formula I used to use all the time before Tables came along. My list of names was in Sheet 1 in a Table called Names which in Column C starting in row 12 and has 9 non blank entries before it starts.
So I had my Named Range “Names” using Structured References reference like this:
=tblSheet1[Names] this is the Table Name tblSheet1 and the Column heading Names
Now realising the problem I created Named Range “Names2” using dynamic range references:
=’Sheet1′!C$12:INDEX(‘Sheet1’!$C:$C,COUNTA(‘Sheet1’!$C:$C)+9) the last ‘9’ is taking care of the 9 non blank entries before the data starts
Then this Named Range Names2 is used in the data validation formula in the data validation dialog ie the formula is =Names2, Debra’s event BA code works beautifully on the double click.
The only other issue to be aware of if you want to find the Combobox control later to change a setting say to change from 15 to show in the list to 20,then it’s a bit hard to find as the code has made it invisible. To overcome this issue I used this method but probably there is better using VBA in the immediate window. So my ‘on the fly’ method for this issue was on the worksheet Ribbon go to Developer and Design Mode then used F5 Goto Special then Objects all controls are highlighted including the Combobox. I then widen it as it was hiding. Then separately select it again and right click for properties.
After I had wrote this Debra kindly responded to an email I had sent earlier re this problem and as always she was onto this issue and here is a link on her take on this issue and another possible solution. Debra sells a Pro version of this solution for a modest $20 the last time I looked, which is money well spent if you have a lot of long dropdown lists.