Referencing other worksheet data using a drop-down list

[matlocktm]

What I'd like to accomplish is difficult to explain, as I am an Excel
novice, but here goes: I am creating a timecard using a worksheet
(labeled "timecard"), and on a second worksheet (labeled "employee
data") I've created a simple table consisting of two columns, one
labeled "employee name" and the other labeled "employee ID", for which
I am using to reference. I created a drop-down list of employee names
on the first worksheet that references the "employee name" data from
the second worksheet. Here's where I am at a loss: In a cell on the
first worksheet, I'd like to have the "employee ID" automatically fill
in based on which "employee name" is selected from the drop-down list.
Does anyone know how to accomplish this? I assume the "employee name"
and "employee ID" on the second worksheet need to reference each other
somehow, but I am unsure of this. Any help is extremely appreciated.
Thanks.

 

[Tom Ogilvy]

assume you put the employee name in Cell A1 of TimeCard

in B1
=if(iserror(match(A1,Employee_Name,0)),"",Index(EmployeeID,match(A1,Employee
_Name,0),1)

this assume you have defined names for the ID (EmployeeID) and Name
(Employee_Name).

Insert=>Name=>Define

You would need to do that for the names to use a data=>validation dropdown.

 

[Gord Dibben]

Non-VBA approach.

On sheet1 with the DV drop-down list in A1.

In A2 enter =VLOOKUP(A1, Sheet2!$a$1:$b$20)

On Sheet2 enter your list of "names" in column A and your list of
corresponding ID numbers in column B

When you select a name from the drop-down the ID number will show up in A2 on
sheet1.

Gord Dibben Excel MVP

 

[Tom Ogilvy]

In A2 enter =VLOOKUP(A1, Sheet2!$a$1:$b$20)

probably should be

In A2 enter =VLOOKUP(A1, Sheet2!$a$1:$b$20,2,False)


Equivalent to what I presented, but I didn't assume names was to the left of
id's as this requires.

 

[matlocktm]

Thanks for your help, Tom and Gord. I tested both solutions to
maximize my learning:

1) On Sheet1, B1: =IF(ISERROR(MATCH(A1,EmployeeName,0)),"",INDEX(EmployeeID,MATCH(A1,EmployeeName,0),1))

2) On Sheet1, C1: =IF(ISERROR(VLOOKUP(A1,
Sheet2!$A$1:$B$100,2,FALSE)),"",VLOOKUP(A1,
Sheet2!$A$1:$B$100,2,FALSE))

Note that 2) includes an IF statement as well as VLOOKUP to avoid the
#N/A when cell A1 is blank.

Thanks again, fellas. I couldn't have done this without the guidance.

Best regards,

Tina Matlock