Hi All
I would really appreciate your help with what I believe will be a nested SUMPRODUCT within an IF statement (or it may involve VLOOKUP, HLOOKUP, any other formula!).
Working with the NHS, I am trying to devise a simple Demand and Capacity tool where a service can monitor the number of activities (appointments) available each day across multiple sites. This is standardised each day but will be affected by people taking leave, being off sick etc.
Using SUMPRODUCT I have been able to calculate the number of a given activity lost should a person be off (i.e. Person A is off on Monday so 4 episodes of activity 1 will be unavailable), but now I need to add additional criteria by identifying the Site that the activities will be lost from.
I have attached a sample of the spreadsheet I am using. The first sheet represents the activities each person is scheduled to undertake each day and the location the activities take place at (these do not change), and the second sheet identifies who is off on any given day.
The third sheet includes the table I am unable to complete. This table needs to identify the total number of activities (split by activity no.) against each site for the date identified at the top of the sheet. To do this it needs to look at the Master Shift Grid and identify the number of activities per site for that day (which is the easy part) and then remove any activities for people who are on leave.
For example, looking at the Master Shift Grid of the attached, on a Monday Person A does 4 episodes of Activity 1 at Site 1, and Person B also does 4 episodes of Activity 1 at Site 1 (highlighted in red). I can get the table on the third sheet (Lost slots per site) to calculate that on a Monday (01/07/13 in this example) there are 8 episodes of Activity 1 at Site 1 available, but what I can’t get it to calculate is if Person A is on leave on 01/07/13 there needs to be only 4 episodes of Activity 1 at Site 1 available for that day.
Is anyone able to help me with this please?
Apologies for the long post but its not a simple thing to explain!
Thanks in advance
00skins
I would really appreciate your help with what I believe will be a nested SUMPRODUCT within an IF statement (or it may involve VLOOKUP, HLOOKUP, any other formula!).
Working with the NHS, I am trying to devise a simple Demand and Capacity tool where a service can monitor the number of activities (appointments) available each day across multiple sites. This is standardised each day but will be affected by people taking leave, being off sick etc.
Using SUMPRODUCT I have been able to calculate the number of a given activity lost should a person be off (i.e. Person A is off on Monday so 4 episodes of activity 1 will be unavailable), but now I need to add additional criteria by identifying the Site that the activities will be lost from.
I have attached a sample of the spreadsheet I am using. The first sheet represents the activities each person is scheduled to undertake each day and the location the activities take place at (these do not change), and the second sheet identifies who is off on any given day.
The third sheet includes the table I am unable to complete. This table needs to identify the total number of activities (split by activity no.) against each site for the date identified at the top of the sheet. To do this it needs to look at the Master Shift Grid and identify the number of activities per site for that day (which is the easy part) and then remove any activities for people who are on leave.
For example, looking at the Master Shift Grid of the attached, on a Monday Person A does 4 episodes of Activity 1 at Site 1, and Person B also does 4 episodes of Activity 1 at Site 1 (highlighted in red). I can get the table on the third sheet (Lost slots per site) to calculate that on a Monday (01/07/13 in this example) there are 8 episodes of Activity 1 at Site 1 available, but what I can’t get it to calculate is if Person A is on leave on 01/07/13 there needs to be only 4 episodes of Activity 1 at Site 1 available for that day.
Is anyone able to help me with this please?
Apologies for the long post but its not a simple thing to explain!
Thanks in advance
00skins