How to automatically rotate cashier name list base on working schedule?

[Manith]

Hi All,

May I ask how to automatically rotate cashier schedule (in Part 2) based on their working schedule (in Part 1)? So if they have day off/AL/PH in any day then their cashier schedule will be automatically change and rotate.
Part 1: is the working schedule.
Part 2: is the cashier schedule (has 3 shifts) which need to be rotating based on the working schedule. Please note that staffs working in morning shift will always work as cashier in shift 1. And Evening staffs will be working for only shift 2 and shift 3.
Part 3: is show the many times of each staffs work as cashier in each shift because we want to show them working in each shift equally.

Remark:
D = Day Off
PH = Public Holiday
M =Morning Shift
E = Evening Shift

The below is the example:

Book2.xlsx
	A	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
3	Part 1	Name	Working Shift	1-Jan	2-Jan	3-Jan	4-Jan	5-Jan	6-Jan	7-Jan	8-Jan	9-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	16-Jan	17-Jan	18-Jan	19-Jan	20-Jan	21-Jan	22-Jan	23-Jan	24-Jan	25-Jan	26-Jan	27-Jan	28-Jan	29-Jan	30-Jan	31-Jan
4				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	Mon
5		A1	M	M	M	D	M	PH	M	M	M	M	D	M	M	M	M	M	D	M	M	M	M	M	M	M	D	M	M	M	M	M	M	D
6		A2	M	M	M	M	D	M	M	M	M	M	M	D	M	M	M	M	M	D	M	M	M	M	M	M	M	D	M	M	M	M	M	M
7		A3	M	M	M	M	M	D	M	M	M	M	M	PH	D	M	M	M	M	M	D	M	M	M	M	M	M	M	D	M	M	M	M	M
8		A4	M	M	M	M	M	M	D	M	M	M	M	M	M	D	M	M	M	M	M	D	M	M	M	M	M	M	M	D	M	M	M	M
9		B1	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D
10		B2	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E
11		B3	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E
12		B4	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E
13		B5	E	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E
14		B6	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E
15		B7	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E
16		B8	E	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E	E	E	E	D	E	E	E
17
18
19			Cashier Shift Rotation
20		Part 2	Date	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
21				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	Mon
22			Shift 1	A1	A2	A3	A4	A2	A1	A3	A4	A1	A2	A4	A1	A3	A2	A4	A3	A1	A2	A3	A4	A1	A2	A3	A4	A1	A2	A3	A4	A1	A2	A3
23			Shift 2	B1	B3	B5	B7	B2	B4	B6	B8	B1	B3	B5	B7	B2	B4	B6	B8	B3	B1	B5	B8	B2	B4	B6	B7	B3	B4	B5	B1	B2	B4	B6
24			Shift 3	B2	B4	B6	B8	B1	B3	B5	B7	B2	B4	B6	B8	B1	B3	B5	B7	B2	B4	B7	B6	B1	B3	B5	B8	B1	B2	B6	B7	B8	B3	B5
25
26
27		Part 3	Shift	A1	A2	A3	A4	B1	B2	B3	B4	B5	B6	B7	B8
28			1	8	8	8	7	0	0	0	0	0	0	0	0
29			2	0	0	0	0	4	4	4	5	4	4	3	3
30			3	0	0	0	0	4	4	4	3	4	4	4	4
31			Total	8	8	8	7	8	8	8	8	8	8	7	7
CASHIER-FEB-22

Cell Formulas
Range	Formula
D28:O28	D28	=COUNTIF($D22:$AH22,D27)
D29:O29	D29	=COUNTIF($D23:$AH23,D27)
D30:O30	D30	=COUNTIF($D24:$AH24,D27)
D31:O31	D31	=SUM(D27:D30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:AH16	Cell Value	contains "PH"	text	NO
D16:AH16	Cell Value	contains "E"	text	NO
D16:AH16	Cell Value	contains "D"	text	YES
D3:AH4	Cell Value	contains "SUN"	text	NO
D3:AH4	Cell Value	contains "SA"	text	NO
D5:AH15	Cell Value	contains "E"	text	NO
D5:AH15	Cell Value	contains "D"	text	YES

 

[Fluff]

Duplicate to: How to rotate name list?

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.