Complicated Function Question

[Matthew]

I need help setting up a complicated function...if it can be done.

What I have is a cell that uses data validation (list). The list is a list
of employees that is in a different worksheet. I would like to be able to
select an employees name from the drop down list (I've got that part
working). After selecting the employees name the next row contains a place
to enter the number of hours that were worked on a job. I would like for the
3rd row to calculate the labor cost to the company based on the name (row
1-dropdown) multiplied by the number of hours worked on the project (second
row). I know that I will have to put the hourly cost of the employee in a
list, but the formula for this function is way too complicated for me to
figure out....help.

 

[Pete_UK]

You already have part of your table - the list of names on the second
sheet. Put the hourly cost for each employee in column B, assuming the
names are in column A (Let's assume you have 20 of them, so this table
will occupy A1:B20 on Sheet2).

Assume your drop-down is in cell A1 of Sheet1, and that you want to
use A2 to enter the number of hours. Put this formula in A3:

=VLOOKUP(A1,Sheet2!A$1:B$20,2,0)*A2

and format this cell as currency.

Hope this helps.

Pete

 

[Matthew]

Pete,
When I put the formula in it says that there is a circular reference...maybe
I transposed it wrong.

The table occupies A1:B16 of sheet 3
The drop down in on A54 of sheet 1
The number of hours is in B54...so I did the formula as follows:

=VLOOKUP(A54,Sheet3!A$1:B$16,2,0)*B54

What did I do wrong?

Thanks for you help so far.

 

[Bob Phillips]

As long as you have not entered that in A54 or B54 you should be fine.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)