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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ROW/COL[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1 (HEADER)[/TD]
[TD]User[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aaron[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bernie[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Charlie[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]David[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Eric[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Frank[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]George[/TD]
[TD]$75[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Harry[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Ian[/TD]
[TD]$175[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jack[/TD]
[TD]$80[/TD]
[/TR]
</tbody>[/TABLE]

"EFFORT" TABLE
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ROW/COL[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1 (HEADER)[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Effort (Hours)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aaron[/TD]
[TD]1/1/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aaron[/TD]
[TD]1/2/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bernie[/TD]
[TD]2/7/2016[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ian[/TD]
[TD]1/7/2016[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jack[/TD]
[TD]1/12/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Eric[/TD]
[TD]1/22/2016[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bernie[/TD]
[TD]2/1/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Eric[/TD]
[TD]2/1/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Harry[/TD]
[TD]2/4/2016[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Aaron[/TD]
[TD]2/4/2016[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Frank[/TD]
[TD]4/7/2016[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Ian[/TD]
[TD]4/9/2016[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Eric[/TD]
[TD]5/3/2016[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Jack[/TD]
[TD]5/1/2016[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Aaron[/TD]
[TD]3/7/2016[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Bernie[/TD]
[TD]2/6/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Frank[/TD]
[TD]4/11/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Jack[/TD]
[TD]5/5/2016[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Eric[/TD]
[TD]6//2016[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Jan 2016[/TD]
[TD]Feb 2016[/TD]
[TD]Mar 2016[/TD]
[TD]Apr 2016[/TD]
[TD]May 2016[/TD]
[TD]Jun 2016[/TD]
[/TR]
[TR]
[TD]Aaron[/TD]
[TD]4+4=8[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bernie[/TD]
[TD]0[/TD]
[TD]3+1+1=5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3+8=11[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7+8=15[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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!