Shorting a data validation List

[vetteguy]

I have a table in a different workbook that I enter employee info. I have a NameID, Name, email,phone and state columns. I use a data validation drop down for the NmaeID and then vlookup to speed up the data entry on a different workbook 1. The list of employees will be very long and I was hoping to use the state column to shorten the NameID drop down. One cell for the criteria state=TX and then employees in that state would show up in the drop down as a choose.

Workbook 1 sheet 1

state

TX

<tbody>
</tbody>

NameID	Name	Phone	Email
smithJ= only show TX employees as a choice	John Smith	233232323	smithj@gmail.com

<tbody>
</tbody>

I use vlookup to fill in Name, Email, phone off of workbook 2


Workbook 2 sheet 1

NameID	Name	Email	Phone	State
smithJ	John Smith	smithj@gmail.com	23232323223	TX
HallT	Tim Hall	HallTgmail.com	32323222323	CA

<tbody>
</tbody>

Thanks for your help

 

[L. Howard]

You may want to use a dependant dropdown list, where you select the state from one dropdown and all employees in that state are listed in the dependant dropdown.
Of course you would have to know what state the employee was in to start with, right? Maybe that is not a problem but if the number of employees is so long you want to shorten your search this may be taking you out of the fry pan into the fire.

Debra D. does a great job here to get you going with depentent drop-downs.

Dynamic Dependent Excel Drop Downs | Contextures Blog

Perhaps another way to go is with a single drop down where your list is like this:

TX followed by all the Texas employees
OK followed by all the Oklahoma employees

And so forth for all the states you have employees.

Now you select the cell with the State/Employee drop down list.
Type in the state TX, for instance, and DO NOT HIT ENTER.
Click the down arrow of your drop down and it will take you to the top of the TX list of employees.

Regards,
Howard

 

[vetteguy]

Thank you Howard. I will look at Debra D. info. I do have part of my employee info last column is the state. I will try that and see if the drop down changes to the TX employees on top.