Drop-down list

[tapsmiled]

I am using a drop-down list of roughly 400 employee names, entered alphabetically. Is there a way that if the user begins to type one of the names that is in the drop-down validation list, Excel can "auto-fill" the rest of the name? Or, is there a way to simplify the drop-down list so that the user is not necessarily having to scroll through the entire list of 400?

[mrice]

You can achieve something similar to this by having the having the 400 names in a column and then hide the 400 rows. When a user starts to type in any row below this, the auto-complete kicks in as soon as there is an unambiguous match.

A more sophisticated way of meeting your requirement is to create a user form with a combo-box and then use its change event to dynamically change the list of options. Please post back if you are unable to find something existing on this option.

[humdingaling]

an active x (or form) combobox will let you do as described
whereby you type in first few letters of the name and it will go to that section

For Active X set -
listfilledrange as your list (employee)
linked cell as your output cell

you can change around the 2 match criteria (matchentry/matchrequired) if you want to control input/output a bit more

[tapsmiled]

The Active X portion of what you just described is foreign to me. I would attach a copy of the worksheet, but it is full of names already. Basically, I have a sheet that is a Master List with 400 employees. That sheet also calculates various pieces of info such as review dates and sick time accruals based upon date of hire. The intent is that I will have another sheet for the users who will be assigned 10-15 employees to supervise. So, they will choose their assigned employees, and all of the other related info will auto-populate. The problem is that I do not receive the information in alphabetical order but, rather, by date of hire. Compounding that is the issue that the list of employees is not complete. Other exisiting employees from other locations will, eventually, be added to the list...all with varying dates of hire. So, it is easiest to leave my master form in order by date of hire. As the list changes, I only need to replace the first 4 columns (name, ID number, date of hire, and location), and all of the other calculations will happen on their own.

So after that long diatribe, the problem here is that the Data Validation I used for my drop-down presents the names in non-alphabetical order. Is there a way to validate the data so that they appear in alphabetical (without having to sort the Master Sheet)?

[humdingaling]

this is rather different to your initial request

To tackle the working of DV/combobox + non alphabetical list

i have generated list of random names/ID numbers/Dates/locations (using online randomizer)
essentially i have enlisted a helper column (Column F)

DV is in H6
Combobox is in h4

you can sort by either column A/B/C/D and the DV/Combobox list will always be alphabetical order

[tapsmiled]

How did you generate the drop-down box with the scroll bar? I resolved my issue in a similar fashion, but my drop-down does not have the scroll bar which makes things more difficult to manage.

[tapsmiled]

Disregard. Mine does have a scrollbar.

[humdingaling]

if this does suit your requirements and you want to learn more about activex combobox

more can be found here
http://blog.contextures.com/archives...cel-worksheet/

also i didnt credit it before but i used this for the sorting and it explains why/how it works
http://www.get-digital-help.com/2009...array-formula/

[tapsmiled]

Thank you for the information.