Hi,
I've been banging my head against a wall with this and I'm reaching out to the internet for some wisdom and help.
I have a basic spreadsheet that shows a request for the cover of hours which is then either then picked up by Group 1, Group 2 or Group 3.
What I need to do is show in the report a summary of the requests for hours cover in each four weekly period along with the hours that are/were outstanding. I originally tried a pivot table which I can do for a single date range but I understand it isn't possible to show multiple date ranges in one pivot table, the use of multiple pivot tables isn't practical as I have a number of areas that I need to duplicate the below for.
I've tried COUNTIFS and IF statements referencing the date periods in column A and B but I just can't get it to work.
Glad to be here and looking to learn.
Report
<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="111"><col width="114"></colgroup> <tbody>
</tbody>
Data
<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>
I've been banging my head against a wall with this and I'm reaching out to the internet for some wisdom and help.
I have a basic spreadsheet that shows a request for the cover of hours which is then either then picked up by Group 1, Group 2 or Group 3.
What I need to do is show in the report a summary of the requests for hours cover in each four weekly period along with the hours that are/were outstanding. I originally tried a pivot table which I can do for a single date range but I understand it isn't possible to show multiple date ranges in one pivot table, the use of multiple pivot tables isn't practical as I have a number of areas that I need to duplicate the below for.
I've tried COUNTIFS and IF statements referencing the date periods in column A and B but I just can't get it to work.
Glad to be here and looking to learn.
Report
| A | B | C | D | E | F | G | H | I | J | |
| 1 | Period | Group 1 | Group 2 | Group 3 | Outstanding | |||||
| 2 | Start | End | Cover | Hours | Cover | Hours | Cover | Hours | Cover | Hours |
| 3 | 05/04/15 | 02/05/15 | COUNT of Group 1 in date range | SUM of Group 1 hours in date range | COUNT of cover not assigned to any Group in date range | SUM of hours not assigned to any Group hours in date range | ||||
| 4 | 03/05/15 | 30/05/15 | ||||||||
| 5 | 31/05/15 | 27/06/15 | ||||||||
| 6 | 28/06/15 | 25/07/15 | ||||||||
| 7 | 26/07/15 | 22/08/15 | ||||||||
| 8 | 23/08/15 | 19/09/15 | ||||||||
| 9 | 20/09/15 | 17/10/15 | ||||||||
| 10 | 18/10/15 | 14/11/15 | ||||||||
| 11 | 15/11/15 | 12/12/15 | ||||||||
| 12 | 13/12/15 | 09/01/16 | ||||||||
| 13 | 10/01/16 | 06/02/16 | ||||||||
| 14 | 07/02/16 | 05/03/16 | ||||||||
| 15 | 06/03/16 | 02/04/16 |
<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="111"><col width="114"></colgroup> <tbody>
</tbody>
Data
| A | B | C | D | E | F | |
| 1 | Date Request Received | Staff Member Requesting | Unique Request ID | Weekly Hours Cover Required | Group Covering | Cover Provided By |
| 2 | e.g. 05/04/2015 | e.g RH | e.g 123456 | e.g 10.5 | e.g Group 1 | e.g Unit 1 |
<colgroup><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"><col width="86"></colgroup> <tbody>
</tbody>
