Auto Complete Data Validation

Dear all,

I'll refer to a previously solved post that I'd rather not hijack - specifically the solution from HSV.

http://www.excelforum.com/excel-gene...tion-list.html

Solution is: http://www.excelforum.com/attachment...-sample-1-.xls

In a small sample number of names like in this example it works fine. I was trying to adapt the file to extend the number of names on Sheet2 to several hundred. Something goes wrong in the validation and/or defined names and the clever autocomplete boxes don't seem to work properly. The validation suggests the first appropriate value - which still might not be the one you're looking for - and then a lot of the irrelevant values follow. This is ok if the first name is the one you want, but you can't expect 2 appropriate suggestions. Can someone explain how the named ranges in this example work, and whether or not it's possible to make this work with a lot of names without the system collapsing?

The autocomplete function doesn't seem necessary if the list remains brief.

Muchos gracias.

two things to check

is your list in alphabetical order?

also you need to extend the Named range "names"
=INDIRECT("sheet2!$A$2:$A$"&COUNTA(Sheet2!$A$2:$A$105)+1)
only goes up to 105 in the example
extend this range and it should work