Auto Sort Big Validation List Using First Letter - list in external worksheet

  • This is a problem that I have been working on for the last two days and have finally come up with a reasonably good solution/outcome. Many people seem to want to do this, especially small businesses with an extensive list of customer names or a product list that is connected to VLOOKUPs. They want to be able to select a name from a validation drop down list and have all the other variables auto change...which many are able to do...but what if there are 100s or even thousands of names within the list? You want to be able to enter the first letter and have the list automatically narrow-down. The following will achieve pretty much exactly that:


    I will assume your list data is in a separate worksheet or workbook.


    Make a reference list to the list data on another worksheet. Name that reference list. Now in the cell DIRECTLY underneath the list (same column), create validation list...make source ="whatevernameyougavethereferencelist". You should now be able to use a simple form of autocomplete in the validation cell...but we're not finished.


    You clearly don't want this unwieldy reference list (which could be 100s or 1000s in length) to appear on the page...so you should now hide all those rows.


    Uh-oh, now the autocomplete won't work for the validation cell!!! Don't worry...this happened to me when I tried to hide the reference list (and I have no idea why). Here's how to get around it...follow all the instructions on this page:


    http://www.contextures.com/xlDataVal10.html


    One part will be cutting and pasting VBA code. Not actually very hard, I am a complete novice but still could do it...just follow the instructions carefully.


    Done!


    Now you should be able to double-click on the validation cell and be able to select from the list using the drop-down arrow. With the list open in the drop-down box...you can simply type the first letter you want e.g. "m" and the list will auto highlight the first "m" word. Type "m" then "a" and it will go to the first word starting with "ma" and so on. The validation cell will also be autocompleting with the nearest on the list.


    You might find it useful also to play around with the preferences of the combo box. I found it useful to extend the size of the drop-down list to 15 rows instead of the automatic 8...I can see more names at once when I'm scanning our big list of 3000 customer names.

  • Re: Auto Sort Big Validation List Using First Letter - list in external worksheet


    Simon, hiding rows doesn't stop AutoComplete from working and there is no need for the code and use of other controls as shown on the page you link to.

  • Re: Auto Sort Big Validation List Using First Letter - list in external worksheet


    Hi Dave,


    For some reason, when I hid the rows, the autocomplete normally associated with a validation cell didn't work. But that is a bit beside the point I guess, because the real issue was trying to get an autocomplete that worked much better than the one that you usually get with the validation cell...i.e. I needed to be able to enter a single letter and the list would self highlight the first word containing that letter (pretty much wanted an autocomplete like you can find on the web e.g. Google Search autocomplete)...only the combo box with the VB code entered would do this job.


    Perhaps this needs to be classified as a "complex autocomplete" as it more complex (and has more capability) than the usual autocomplete.

  • Re: Auto Sort Big Validation List Using First Letter - list in external worksheet


    The way I have written my first post sounds like I was trying to "get around" the problem of the Autocomplete function disappearing when the above rows are hidden by inserting VB Code. I didn't mean to say this.


    What I meant to say was that by following the instructions on the external webpage (http://www.contextures.com/xlDataVal10.html) and adding a combo box and inserting the VB Code, that the Autocomplete can be GREATLY ENHANCED in terms of functionality compared to the usual Autocomplete.


    The result is that one can easily scan through a huge validation list by just entering the first letters.