Formula to Auto Generate and change shift Duty Weekly

[Lukma]

HI Guys

Its Nice been back online with you friend and i appreciate every one in the forum of been great help and assistance

I need a Solution With a Dynamic Auto Shuffle Shift Change roster just with on Click to Change each employee

1. Morning Shift as ( M ) 2. Afternoon Shift as ( A ) 3. Night shift as ( N )
What i need formula is when i click the following Month it should change the shift and change duty in groups

Total Number of Clerk is 19 and i need to group them Morning and afternoon and night
is there any way i could have a formula to do this shuffling one i click the next month without starting over manually

I wil appreciate if there is a solution to Auto Generate for every month

Planning Supply Chain Duty Roster.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2		Morning Shift	M	Planning Supply Chain Schedule Roster
3		Afternoon Shift	A
4		Night Shift	N
5		OFF DAYS	O
6		LEAVE	L
7		Over Time	OT
8	Month	Jan-2021				30 Day	8 HRS	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun
9	Emp ID	Employee Names	Title	On Duty	Off	Leave	OverTime	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
10	165317	Ibrahim Almazrouei	Officer	31	0	0	0	O	O	A	A	A	A	A	O	O	A	A	A	A	A	O	O	A	A	A	A	A	O	O	A	A	A	A	A	O	O	A
11	160858	Ali Al Marzouqi	Officer	31	0	0	0	O	O	A	A	A	A	A	O	O	M	M	M	M	M	O	O	A	A	A	A	A	O	O	M	M	M	M	M	O	O	A
12	160194	Mohamed Sunkar	Officer	31	0	0	0	O	O	M	M	M	M	M	O	O	A	A	A	A	A	O	O	M	M	M	M	M	O	O	A	A	A	A	A	O	O	M
13	161503	Butti Al Mehairbi	Officer	31	0	0	0	O	O	A	A	A	A	A	O	O	M	M	M	M	M	O	O	A	A	A	A	A	O	O	M	M	M	M	M	O	O	A
14	155725	Mohamad Ismail	Officer	30	0	0	1	A	OT	M	M	M	M	M	M	M	A	A	A	A	A	A	O	M	M	M	M	M	M	M	A	A	A	A	A	A	A	M
15	156312	Emmanuel Ovie	Officer	31	0	0	0	A	A	N	N	N	N	N	N	N	A	A	A	A	A	A	A	M	M	M	M	M	M	M	A	A	A	A	A	A	A	N
16	155726	Noushad Arinchira	Officer	31	0	0	0	M	M	A	A	A	A	A	A	A	M	M	M	M	M	M	M	A	A	A	A	A	A	A	M	M	M	M	M	M	M	A
17	155705	Mohamed Hashim	Officer	2	0	0	0	A	A
18	156228	Oscar Omokhudu	Clerk	0	0	0	10	OT	OT						OT	OT						OT	OT						OT	OT						OT	OT
19	155716	Mortada Hussein	Clerk	0	0	0	10	OT	OT						OT	OT						OT	OT						OT	OT						OT	OT
20	156079	Mohamed Ashour	Clerk	0	0	0	10	OT	OT						OT	OT						OT	OT						OT	OT						OT	OT
21	156250	AbdulKhader Shinan	Clerk	0	0	0	10	OT	OT						OT	OT						OT	OT						OT	OT						OT	OT
Employee Duty Roster

Cell Formulas
Range	Formula
I8:AM8	I8	=I9
I9	I9	=C8
J9:AJ9	J9	=I9+1
AK9	AK9	=IF(DAY(AJ9+1)<=3,"",AJ9+1)
AL9	AL9	=IF(DAY(AJ9+2)<=3,"",AJ9+2)
AM9	AM9	=IF(DAY(AJ9+3)<=3,"",AJ9+3)
B10:D21	B10	=IF('Employee Master'!B3="","",'Employee Master'!B3)
E10:E21	E10	=COUNTIF(I10:AM10,"?")
F10:F21	F10	=COUNTIF(I10:AM10,$D$5)
G10:G21	G10	=COUNTIF(I10:AM10,$D$6)
H10:H21	H10	=COUNTIF(I10:AM10,$D$7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I10:AM51	Expression	=OR(TEXT(I$9,"DDDD")=Setting!$F$2,TEXT(I$9,"DDDD")=Setting!$F$3)	text	NO
B9:AM48	Expression	=AND($B10<>"",B$9<>"")	text	NO
AM9	Cell	does not contain a blank value	text	NO
I9:AL9	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C8	List	=Month
I10:AM21	List	=Shift_List