Automated shift allocation based on target values by hour

[visionnz]

Hi Thier,
just wondering if anyone could help with this? or if its even possible?

Im looking to create a shift allocation the objective is to use some form of formula to automatically allocate the a 7.5 hour shift to the available staff in the most efficient way possible to meet the forecasted staffing requirements, although this could be done manually the overall project will 24 hour shift structures across multiple locations.

hopefully the mock up below helps, any help you could offer would be appreicated or even just a no this cant be done using excel?

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	*	*	*	Staff Required based on erlang	6	5	10	9	8	10	9	8	13	9	10	11	10	6	5	4	3	12	1
2	*	*	*	Staff Scheduled by period	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	0	0	0	0
3	Time Period	Start Time	End Time	Labour Hours *Per employee	8:00 a.m.	8:30 a.m.	9:00 a.m.	9:30 a.m.	10:00 a.m.	10:30 a.m.	11:00 a.m.	11:30 a.m.	12:00 p.m.	12:30 p.m.	1:00 p.m.	1:30 p.m.	2:00 p.m.	2:30 p.m.	3:00 p.m.	3:30 p.m.	4:00 p.m.	4:30 p.m.	5:00 p.m.
4	Employee 1	8:00 a.m.	3:30 p.m.	7.5	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	*	*	*	*
5	Employee 2	8:00 a.m.	3:30 p.m.	7.5	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	*	*	*	*
6	Employee 3	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
7	Employee 4	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
8	Employee 5	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	Employee 6	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
10	Employee 7	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	Employee 8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
12	Employee 9	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
13	Employee 10	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1