How to combine tables into a pivot and dynamically change rates

[shawnw]

I have two worksheets:

1. People (shows names and hourly rates). Every row is unique. Some names that appear here will not appear on the "Effort" sheet, but this is not known in advance. The names are NOT sorted (e.g., alphabetically).
2. Effort (shows names of people who worked on specific dates -- and a bunch of other stuff). Every row is unique. No names appear here that do not appear in the "People" sheet. The effort is NOT sorted (e.g., by date).

I am trying to create a pivot table that shows:

1. Row: Everyone's name, rate, and hours worked
2. Column: The month/year that effort was recorded

"PEOPLE" TABLE

ROW/COL	A	B
1 (HEADER)	User	Rate
2	Aaron	$100
3	Bernie	$100
4	Charlie	$100
5	David	$125
6	Eric	$150
7	Frank	$100
8	George	$75
9	Harry	$125
10	Ian	$175
11	Jack	$80

<tbody>
</tbody>

"EFFORT" TABLE

ROW/COL	A	B	C
1 (HEADER)	Name	Date	Effort (Hours)
2	Aaron	1/1/2016	4
3	Aaron	1/2/2016	4
4	Bernie	2/7/2016	3
5	Ian	1/7/2016	2
6	Jack	1/12/2016	4
7	Eric	1/22/2016	2
8	Bernie	2/1/2016	1
9	Eric	2/1/2016	8
10	Harry	2/4/2016	6
11	Aaron	2/4/2016	5
12	Frank	4/7/2016	3
13	Ian	4/9/2016	4
14	Eric	5/3/2016	6
15	Jack	5/1/2016	7
16	Aaron	3/7/2016	3
17	Bernie	2/6/2016	1
18	Frank	4/11/2016	8
19	Jack	5/5/2016	8
20	Eric	6//2016	5

<tbody>
</tbody>

Note that there are no hours for Charlie, David, or George.

So the pivot chart should look something like this, which each cell showing the total of the hours for the selected time period (in this case, months). Note that I show the math, but it's the total I really care about.

Name	Jan 2016	Feb 2016	Mar 2016	Apr 2016	May 2016	Jun 2016
Aaron	4+4=8	5	3	0	0	0
Bernie	0	3+1+1=5	0	0	0	0
Charlie	0	0	0	0	0	0
David	0	0	0	0	0	0
Eric	2	8	0	0	6	5
Frank	0	0	0	3+8=11	0	0
George	0	0	0	0	0	0
Harry	0	6	0	0	0	0
Ian	2	0	0	4	0	0
Jack	4	0	0	0	7+8=15	0

<tbody>
</tbody>

IMPORTANT:
What I'd really like to do is multiple the effort (hours) by the rate to get the cost. The catch is that each contract has a term during which rates are set, but the rate foe a single person may change from contract to contract. And contracts/rates can change mid-month. So, for example, Aaron may have a rate of $100 (per hour) from Jan 1 to Feb 15, but starting on Feb 16 the rate jumps drops to $90. So the "People" worksheet is too simple right now, because there is only one rate per person. It really needs multiple rates per person, with (non overlapping) start and end periods for each rate. Then the pivot table would need to multiply the Effort by the appropriate Rate, depending on the date of the effort.

Thanks!