Weighted Average for multiple sheets

I'm trying to calculate a weighted average for 1) the team and 2) for individuals across multiple sheets. The multiple sheet represents an individuals (see below and the data it contains) and for the team summary it is found in sheet 1. In row 8 below on sheet 1, I'm trying to calulate the weighted average by quarter ie Q1, Q2, Q3, Q4 for the entire team (Z1, Z2, Z3, Z4) and for the year (Z5). the unique identifier is the combination of year and quarter. The weight is simply the number of deals done. What formula or set of formula can I use for across multiple sheets to calculated the weighted average? Thx

By changing the 2016 number on sheet 1, all the numbers should update.


Sheet 1 – Summary sheet
Current Year: 2016

A B C D E F
1. Q1 Q2 Q3 Q4 Annual Average
2. Name of person 1 75% 85%
3. Name of person 2 X1 X2
4. Name of person 3 Y1 Y2
5. .
6. .
7. .
8. Team Weighted Avg. Z1 Z2 Z3 Z4 Z5

_______________________________________________

Sheet 2 – Name of Person 1

9. A B C D E
10. Deal Year date Qtr Percentage
11. 1 2016 Feb 1 Q1 70%
12. 2 2016 Mar 1 Q1 80%
13. 3 2016 Apr1 Q2 80%
14. 4 2016 May 1 Q2 90%


Sheet 3 – Name of Person 2
Repeat of the above (Sheet 2) in terms of how data is organized