vba - Excel 2010: how to use autocomplete in validation list

Question

I'm using a large validation list on which a couple of vlookup() functions depend. This list is getting larger and larger. Is there a way to type the first letters of the list item I'm looking for, instead of manually scrolling down the list searching for the item?

I've done some Googling but this suggests that this is indeed possible in earlier versions of Excel, but not in Excel 2010. Hope you guys can help.

Answer 1

Here is a very good way to handle this (found on ozgrid):

Let's say your list is on Sheet2 and you wish to use the Validation List with AutoComplete on Sheet1.

On Sheet1 A1 Enter =Sheet2!A1 and copy down including as many spare rows as needed (say 300 rows total). Hide these rows and use this formula in the Refers to: for a dynamic named range called MyList:

=OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$300,-1),1)

Now in the cell immediately below the last hidden row use Data Validation and for the List Source use =MyList

[EDIT] Adapted version for Excel 2007+ (couldn't test on 2010 though but AFAIK, there is nothing really specific to a version).
Let's say your data source is on Sheet2!A1:A300 and let's assume your validation list (aka autocomplete) is on cell Sheet1!A1.

1. Create a dynamic named range MyList that will depend on the value of the cell where you put the validation

   =OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$1&"*",Sheet2!$A$1:$A$300,0)-1,0,COUNTA(Sheet2!$A:$A))

2. Add the validation list on cell Sheet1!A1 that will refert to the list =MyList

Caveats

1. This is not a real autocomplete as you have to type first and then click on the validation arrow : the list will then begin at the first matching element of your list

2. The list will go till the end of your data. If you want to be more precise (keep in the list only the matching elements), you can change the COUNTA with a SUMLPRODUCT that will calculate the number of matching elements

3. Your source list must be sorted