mafallaize
New Member
- Joined
- Aug 23, 2011
- Messages
- 31
Hey guys,
I've been struggling with a problem for a few days, so decided to test the superior minds of MrExcel. If anyone can solve this, I'd be very grateful! Here's an example table for a staff schedule:
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
Each person needs 2 days off per week (W), and these are normally set the same each week. Sometimes however, days off need to be adjusted to meet requirements - this is shown as negative numbers in the row "On minus required".
So if a day has a negative number, holidays need to be moved to a less busy day to compensate.
Example: 5/08/2016 has -2, so Larry and Steve need their Ws moved to 5/10/2016, because 5/10/2016 is less busy with a 3 in the "On minus required" row.
The "On" row has the formula: =COUNTIF(E5:E9,"On") for each column. So if we use the formula =IF(E4<0,"On","W") a circular reference is produced.
Is there anyway to use formulas (or as a last resort VBA), to assign everyone holidays as required?
I look forward to your replies, thanks!
Mark
I've been struggling with a problem for a few days, so decided to test the superior minds of MrExcel. If anyone can solve this, I'd be very grateful! Here's an example table for a staff schedule:
5/5/2016 | 5/6/2016 | 5/7/2016 | 5/8/2016 | 5/9/2016 | 5/10/2016 | 5/11/2016 | |
On | 4 | 3 | 3 | 3 | 4 | 4 | 4 |
Required | 4 | 3 | 2 | 5 | 5 | 1 | 4 |
On minus required | 0 | 0 | 1 | -2 | -1 | 3 | 0 |
Dave | On | W | W | On | On | On | On |
Larry | On | On | On | W | W | On | On |
George | W | W | On | On | On | On | On |
Henry | On | On | On | On | On | W | W |
Steve | On | On | W | W | On | On | On |
<colgroup><col><col span="7"></colgroup><tbody>
</tbody>
Each person needs 2 days off per week (W), and these are normally set the same each week. Sometimes however, days off need to be adjusted to meet requirements - this is shown as negative numbers in the row "On minus required".
So if a day has a negative number, holidays need to be moved to a less busy day to compensate.
Example: 5/08/2016 has -2, so Larry and Steve need their Ws moved to 5/10/2016, because 5/10/2016 is less busy with a 3 in the "On minus required" row.
The "On" row has the formula: =COUNTIF(E5:E9,"On") for each column. So if we use the formula =IF(E4<0,"On","W") a circular reference is produced.
Is there anyway to use formulas (or as a last resort VBA), to assign everyone holidays as required?
I look forward to your replies, thanks!
Mark