Hi. Looking for a solution to this in an attempt to consolidate weekly sheets in to a monthly overall value.
I have a spreadsheet with 6 sheets. WK1-5 then a Totals sheet.
Sheets WK1-5 are identical in layout to below.
I'm looking to work out how to total each weeks sheet so that there is only one line per staff member and a full total of all 5 weeks figures beside them.
I've attempted a VLOOKUP but can't get it to work and also tried an INDEX/MATCH lookup using arrays to match the staff member to then populate the necessary fields but i think this is a bit above my knowledge levels unfortunately.
Anyone have any ideas?
I have a spreadsheet with 6 sheets. WK1-5 then a Totals sheet.
Sheets WK1-5 are identical in layout to below.
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
5 | Week1 | Possible Sales | Conf Opps | Sales | ||||||
6 | Staff | Sale Number | Grey | Black | Grey | Black | Grey | Black | ||
7 | Megan | 1587 | 1 | 0 | 1 | 0 | 1 | 0 | ||
8 | Megan | 1588 | 1 | 1 | 1 | 1 | 1 | 1 | ||
9 | Megan | 1599 | 0 | 0 | 1 | 1 | 1 | 1 | ||
10 | Alison | 1601 | 0 | 0 | 1 | 1 | 1 | 1 | ||
11 | Gail | 1605 | 0 | 0 | 1 | 1 | 1 | 1 | ||
12 | Robbie | 1612 | 0 | 0 | 1 | 1 | 1 | 1 | ||
13 | Megan | 1622 | 1 | 1 | 1 | 1 | 1 | 1 | ||
14 | Sean | 1630 | 1 | 0 | 1 | 0 | 1 | 0 | ||
WK1 |
I'm looking to work out how to total each weeks sheet so that there is only one line per staff member and a full total of all 5 weeks figures beside them.
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
4 | Totals | Possibles | Opportunities | Captures | |||||
5 | Staff | Grey | Black | Grey | Black | Grey | Black | ||
6 | Megan | 3 | 2 | 4 | 3 | 4 | 3 | ||
7 | Alison | 0 | 0 | 1 | 1 | 1 | 1 | ||
8 | Gail | 0 | 0 | 1 | 1 | 1 | 1 | ||
9 | Robbie | 0 | 0 | 1 | 1 | 1 | 1 | ||
10 | Sean | 1 | 0 | 1 | 0 | 1 | 0 | ||
11 | |||||||||
12 | |||||||||
13 | |||||||||
14 | |||||||||
15 | |||||||||
16 | |||||||||
Totals |
I've attempted a VLOOKUP but can't get it to work and also tried an INDEX/MATCH lookup using arrays to match the staff member to then populate the necessary fields but i think this is a bit above my knowledge levels unfortunately.
Anyone have any ideas?