Populating a wage based on a drop-down list

[MDHolmes]

I'm pretty much self-taught on Excel, and I'm really having trouble with this...

I have two worksheets in my spreadsheet, the first is Labour Tracking, the second Data.

My Data worksheet has a list of seven employees in Column A and their corresponding wage in Column B. The number of employees could go up to ten.

My Labour Tracking worksheet has a dropdown list that's populated from 'Data'!A1:A7. I did that formula successfully : )

I already have a formula in Column S that would multiply Column Q (Time) by whatever the wage is, but I've been manually entering the wage up to this point because I don't know what formula to use. That's a pain :-/ and I'd love to not have to do my own math - Isn't that what Excel is here to help me with?

So my question: I'd like the Wage column (R) on the Tracking worksheet to autopopulate with corresponding worker's wage once an employee is selected from the drop-down list. Is this possible?

If I could figure out a way to do this, then I could also use an IF statement to calculate the total wages for each employee on a particular job, which would be awesome! I'm pretty sure I've got that formula figured out. Just not this one :-/

Thank you so much for any assistance you can provide!!

 

[JamesCanale]

Kept it to one sheet, but it's easily done on different sheets:

MrExcelPlayground8.xlsx
	A	B	C	D	E	F	G
1	One Sheet					Another Sheet
2	Person	Wage	Hours	total		Person	Wage
3	George	$50.00	5	$250.00		Fred	$30.00
4	Mary	$70.00	4	$280.00		Harry	$40.00
5	Jamie	$60.00	3	$180.00		George	$50.00
6	Harry	$40.00	2	$80.00		Jamie	$60.00
7	George	$50.00	6	$300.00		Mary	$70.00
8	Fred	$30.00	7	$210.00
9	Mary	$70.00	8	$560.00
10	Jamie	$60.00	9	$540.00
Sheet6

Cell Formulas
Range	Formula
B3:B10	B3	=VLOOKUP(A3,$F$3:$G$7,2,FALSE)
D3:D10	D3	=B3*C3

Cells with Data Validation
Cell	Allow	Criteria
A3:A10	List	=$F$3:$F$7

 

[jtakw]

Hi,

Try this formula in Tracking sheet R11 copied down:

=VLOOKUP(P11,Data!A$1:B$10,2,0)

 

[MDHolmes]

Thank you both so much!! That was such a quick response. My data entry is going to be so much faster now <3 <3 <3 <3

 

[MDHolmes]

Kept it to one sheet, but it's easily done on different sheets:

Thank you, James - I'm going to have to practice more with yours as I'm not super clear on how it all works. But I really appreciate you responding =)

 

[jtakw]

You're welcome, thanks for the feedback.

 

[MDHolmes]

Maybe I don't have the subsequent formula down...

So now that I have @jtakw 's vlookup formula, I want to be able to calculate each employee's total billing amount. But nothing is coming up when I try to enter my formula in Column AB.

Column AA is populated using =(Data!A1), =(Data!A2), etc...

Am I not using the right formula command maybe?

 

[jtakw]

The first part of your formula, "mikehoffman" does Not exist in Column P, you Only have "mike" in Column P.
I think you should use AA10 instead of the name and Anchor the rows, like: =COUNTIF(P$11:P$26,AA10)

I have no idea what you're trying to do with the 2nd part of the formula COUNTIF(S11:S26,AB) as this formula is in AB10, will have circular referencing issues ??

 

[MDHolmes]

Sorry, that should have been "Mike".
I was thinking I'd use AA10 as well! And now that I think more about it, when I was trying to enter a formula with AA and AB, I failed to enter a cell number, so that could've been the problem.
Basically I'm trying to calculate Mike's total billable amount (each fella's actually). So I thought if I could make the formula find the "Mike" and then calculate the corresponding numbers in that row in column R, I'd have Mike's total billable amount for that particular job.
I'm going to need to take a course...

 

[jtakw]

When you say Column R, do you actually mean Column S, for the billable amount ?