How to bypass circular reference. Assigning holidays to staff schedule.

[mafallaize]

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:

	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

 

[mafallaize]

bump

 

[Chrisdontm]

To circumvent the circular reference you can go to File>Options>Formulas>
And then over to the right is a thing called: " Enable iterative calculations "
Click on the box, and then below that set how many times you want it to circulate,
I think 3 to 4 will do you, but you may want to experiment with it...

 

[mafallaize]

Thanks Chris it worked!