reference in excel

Posted by Jessica on December 14, 2001 5:20 AM

I am trying to create a workbook to track costs. I have to enter employee names and pay rates. These two items dont change much. I would like to be able to enter one number and have it fill in the emplyee name adn pay rate if possible. Do you know how to do that?Thanks for taking the time to read my request.

Posted by Juan Pablo G. on December 14, 2001 7:23 AM

You would have to create another table that consists of: EmployeeID, EmployeeName, PayRate.

The EmployeeID is the number that allows you to uniquely identify each employee, for example:

{"EmployeeID","Name","PayRate";1,"Juan",10;2,"Pablo",20;3,"Jessica",15}

And so on.

Select this table, go to the Name Box (To the left of the formula bar, and type

ETable

Next, in the sheet you are tracking costs, put, again, the same three columns (ID, Name, PayRate). In columns 2 and 3, use this formula, (Assuming, they are in A:C, starting in Row 2)

=VLOOKUP($A2,ETable,2,0)
for Employee Name and this for Pay Rate
=VLOOKUP($A2,ETable,3,0)

Now, when you enter a number in A2, the name and pay rate will appear.

Juan Pablo G.