Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other worksheet data using a drop-down list
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other worksheet data using a drop-down list
assume you put the employee name in Cell A1 of TimeCard
in B1 =if(iserror(match(A1,Employee_Name,0)),"",Index(Em ployeeID,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. -- Regards, Tom Ogilvy "matlocktm" wrote in message om... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other worksheet data using a drop-down list
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. -- Regards, Tom Ogilvy "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On 3 Apr 2004 11:22:02 -0800, (matlocktm) wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing other worksheet data using a drop-down list
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(Emp loyeeID,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 (matlocktm) wrote in message . com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create formula referencing drop down list (data validation) | Excel Discussion (Misc queries) | |||
Referencing a Drop-Down List | Excel Discussion (Misc queries) | |||
If referencing drop-down list | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions |