Auto-Complete In Datavalidation Field

[Nick101]

Hi, I am running Office 2011 For Mac.

Is it possible to have Data Validation set up so that the person can type into the cell and have the text auto complete based on the range that the DV is pulling from?

I'm making a sheet and the range I want to pull from has 500+ values. I can't sit and wait for the list to scroll it takes FOREVER I and am trying to find a solution.

I've found a bunch of threads on other excel forums but they all either lead to a dead end (probably because the writer was on a PC) or the solution just didn't work.

Any ideas?

[DGagnon]

try taking a look at this thread, its not exactly autocomplete, but i was able to make the list filter

http://www.excelforum.com/excel-2007-help/816240-autocomplete-in-drop-down-list-excel2007.html

[Nick101]

Wow, cool.....

I am trying to see your formulas so I can replicate what you did but I'm not able to access them for some reason. Could you elaborate on how you put that together?

Thanks for sharing!
Nick

[DGagnon]

the formulas are in the named ranges, basicaly i made it dynamicaly create the range based on what was selected in the Filter cell, i used a few formulas in combination

to populate that range i used 2 helper columns, one to find if the item was in the filter Col A:

=IF(LEFT(C2,LEN('Front End'!$B$3))='Front End'!$B$3,1,0)

and one to give it a unique value counting only filtered results Col B:

=IF(A2=1,COUNTIF(A$1:A2,1),"")

then in Col F i just listed the numbers from 1 to the highest possible number

in Col G i used a vlookup:

=IFERROR(VLOOKUP(F2,B:C,2,0),"")

then in H1 i put a count, just to know where the last used cell is:

=1048576-COUNTBLANK(G:G)

for the Named Range i used:

=INDIRECT("Names!$G$2:$G$"&Names!$H$1)