Pulling Wage data from one chart to another chart for scheduling

[Jenni]

I have created a spread sheet that lists all of our field
employees in one column with their wages in the next
column. On a seperate worksheet I have created a layout
for scheduling man hours. The goal is to keep the labor
rate of the job at or below the estimated labor rate. I
am using data validation to ensure that the user can only
pick valid employee names. None of that is a problem. The
problem is: once they have selected an employee name, I
would like the cell beside the name to show that
employees wage. I tried writing an IF-THEN statement, but
since I have over 100 employees on the list, the
statement is too long. Is there a better way to do this?

 

[Jon Peltier]

Jenni -

With names in Sheet1!A2:A101 and rates in Sheet1!B2:B101, and with the
selected employee name in Sheet2!C5, enter this in Sheet2!D5, and fill
it down column D as far as needed:

=VLOOKUP(C5,Sheet1!$A$2:$B$101,2,FALSE)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jenni]

That worked great! I have only one problem. I had the top
entry in the employee list set to "none" with an empty
wage rate, so that if they didn't need as many employees
as I allowed spaces for, the Average function would not
include the empty wage in the average. When VLOOKUP tries
to return the empty cell it says #NA. Do you know away I
can work around this. I need to have lots of places for
them to select employees, but I need the average function
to work even if they don't use all of the possible
employees.

 

[Jenni]

Problem solved, I entered text in the field that I didn't
want a wgae in. Thank you, Thank you!