conditional formatting based cells if there are duplicate cells in a range

[jbrown021286]

danny copy shop calendar test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Shop Schedule					November 2024
2						SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	Jeff's Team	Jeffrey	Open	Thursday		27	28	29	30	31	1	2
4		Josh	Open	Monday			Ramy	Josh	Aldo	Jeff	Justin	Carl	3
5		Tuzzo	Close	Tuesday			Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo	4
6		Ramy	Open	Monday			Ciera	Angel	Brayan	Tre	Que	Angel
7		Aldo	Close	Wednesday				Tuzzo	Dylan	Chris	Jada
8	Justin's Team	Brandun	Open	Thursday				Alex		Peter	Tiona	Miguel
9		Israel	Close	Tuesday								Tevin
10		Gerardo	Open	Wednesday								Pat
11		Justin	Close	Friday		3	4	5	6	7	8	9
12	Carl's Team	Angel	Close	Tuesday			Ramy	Josh	Aldo	Jeff	Justin	Justin	2
13		Kedrin	Open	Friday			Carl	Israel	Gerardo	Brandun	Kedrin	Venro	5
14		Tre	Close	Thursday			Ciera	Angel	Brayan	Tre	Que	Bryan
15		Brayan	Open	Wednesday				Tuzzo	Dylan	Chris	Jada
16		Carl	Open	Monday				Alex		Peter	Tiona	Miguel
17	Used cars	Miguel	Close	Saturday								Tevin
18		Venro	Close	Saturday								Pat
19		Tevin	Close	Saturady		10	11	12	13	14	15	16
20		Pat	Close	Saturday			Ramy	Josh	Aldo	Jeff	Justin	Jeff	1
21	Express	Que	Open	Friday			Carl	Israel	Gerardo	Brandun	Kedrin	Brandun	1
22		Dylan	Close	Wednesday			Ciera	Angel	Brayan	Tre	Que
23		Ciera	Close	Monday				Tuzzo	Dylan	Chris	Jada
24		Jada	Open	Friday				Alex		Peter	Tiona	Miguel
25		Alex	Close	Tuesday								Tevin
26		Chris	Close	Thursday								Pat
27		Tiona	Close	Friday		17	18	19	20	21	22	23
28		Peter	Close	Thursday			Ramy	Josh	Aldo	Jeff	Justin	Jeff	1
29							Carl	Israel	Gerardo	Brandun	Kedrin	Ramy	2
30							Ciera	Angel	Brayan	Tre	Que	Angel
31								Tuzzo	Dylan	Chris	Jada
32								Alex		Peter	Tiona	Miguel
33												Tevin
34												Pat
35						24	25	26	27	28	29	30
36							Ramy	Josh	Aldo	Jeff	Justin	Carl	3
37							Carl	Israel	Gerardo	Brandun	Kedrin	Israel	3
38							Ciera	Angel	Brayan	Tre	Que	Aldo
39								Tuzzo	Dylan	Chris	Jada
40								Alex		Peter	Tiona	Miguel
41												Tevin
42												Pat
43						1	2	Notes
44
45
46
47
48
49
50
November

Cell Formulas
Range	Formula
G2:L2	G2	=UPPER(TEXT(G3,"dddd"))
F3:L3	G3	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+1
G4:K10	G4	=teams!B23
L4,L36,L28,L20,L12	L4	=IF(M4=teams!$B$13,teams!$B$14,IF(M4=teams!$C$13,teams!$C$14,IF(M4=teams!$D$13,teams!$D$14,"")))
F1	F1	="November "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11	F11	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+8
G12:K18	G12	=teams!B23
L13,L37,L29,L21	L13	=IF(M13=teams!$B$5,teams!$B$6,IF(M13=teams!$C$5,teams!$C$6,IF(M13=teams!$D$5,teams!$D$6,IF(M13=teams!$E$5,teams!$E$6,IF(M13=teams!$F$5,teams!$F$6,"")))))
L14,L38,L30,L22	L14	=IF(M13=teams!$B$5,teams!$B$7,IF(M13=teams!$C$5,teams!$C$7,IF(M13=teams!$D$5,teams!$D$7,IF(M13=teams!$E$5,teams!$E$7,IF(M13=teams!$F$5,teams!$F$7,"")))))
F19:L19	F19	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+15
G20:K26	G20	=teams!B23
F27:L27	F27	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+22
G28:K34	G28	=teams!B23
F35:L35	F35	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+29
G36:K42	G36	=teams!B23
F43:G43	F43	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:K4,F5:F9,G5:G10,H4:K10	Expression	=DAY(F3)>8	text	NO
F43:H49	Expression	=AND(DAY(F43)>=1,DAY(F43)<=15)	text	NO
F35:L35,F37:F41,F36:K36,G37:K42	Expression	=AND(DAY(F35)>=1,DAY(F35)<=15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:L2	Any value
H43:H50	Any value
I43:L50	Any value
F2	Any value
F3:F9	Any value
F10	Any value
E1	Any value
F1	Any value
G1	Any value

this is a schedule of peoples off days. i need a way to make the names not visible of the people that are off on Saturday of that week. i am thinking the best way to do that is by using a conditional formatting formula but need some help with making the formula to do that. in this example i would need to have cells G5, h7 and h6 in the first row of the calendar formatted. any help or ideas would be appreciated

 

[StephenCrump]

Your question is not at all clear.

How do these three things tie together?

- "duplicate cells in a range"
- "names not visible of the people that are off on Saturday", and
- cells G5, h7 and h6?

 

[jbrown021286]

The way I am thinking that it will best work is that I am going to conditional format each cell with a name in it that is in a Monday, Tuesday, Wednesday, Thursday and Friday block. So that when a cell has a matching name in the Saturday of that week the text in the weekday cell will be the same color as the background and not be readable. The only thing is I am trying to find the most efficient way of achieving the since I have to do it for all 52 week in the calendar. Does that make sense? Also if you have any other suggestions on how to achieve this i am open to ideas

 

[StephenCrump]

Yes, much clearer now, thanks. How about:

	F	G	H	I	J	K	L
1	Nov-24
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34							Pat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(G$3:G3)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO

I've used red and green font just to show better what's happening. You'll want to change this to the background colours white and grey.

 

[jbrown021286]

Yes, much clearer now, thanks. How about:

	F	G	H	I	J	K	L
1	Nov-24
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34							Pat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(G$3:G3)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO

I've used red and green font just to show better what's happening. You'll want to change this to the background colours white and grey.

danny copy shop calendar test.xlsx
	F	G	H	I	J	K	L
1	November 2024
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34							Pat
35	24	25	26	27	28	29	30
36		Ramy	Josh	Aldo	Jeff	Justin	Carl
37		Carl	Israel	Gerardo	Brandun	Kedrin	Israel
38		Ciera	Angel	Brayan	Tre	Que	Aldo
39			Tuzzo	Dylan	Chris	Jada
40			Alex		Peter	Tiona	Miguel
41							Tevin
42							Pat
43	1	2	Notes
44
45
46
47
48
49
50
November

Cell Formulas
Range	Formula
G2:L2	G2	=UPPER(TEXT(G3,"dddd"))
F3:L3	G3	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+1
G4:K10	G4	=teams!B23
L4,L36,L28,L20,L12	L4	=IF(M4=teams!$B$13,teams!$B$14,IF(M4=teams!$C$13,teams!$C$14,IF(M4=teams!$D$13,teams!$D$14,"")))
F1	F1	="November "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11	F11	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+8
G12:K18	G12	=teams!B23
L13,L37,L29,L21	L13	=IF(M13=teams!$B$5,teams!$B$6,IF(M13=teams!$C$5,teams!$C$6,IF(M13=teams!$D$5,teams!$D$6,IF(M13=teams!$E$5,teams!$E$6,IF(M13=teams!$F$5,teams!$F$6,"")))))
L14,L38,L30,L22	L14	=IF(M13=teams!$B$5,teams!$B$7,IF(M13=teams!$C$5,teams!$C$7,IF(M13=teams!$D$5,teams!$D$7,IF(M13=teams!$E$5,teams!$E$7,IF(M13=teams!$F$5,teams!$F$7,"")))))
F19:L19	F19	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+15
G20:K26	G20	=teams!B23
F27:L27	F27	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+22
G28:K34	G28	=teams!B23
F35:L35	F35	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+29
G36:K42	G36	=teams!B23
F43:G43	F43	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
CalendarYear	=January!$O$3	F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F1
WeekStart	=January!$O$10	F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G4:K10	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(G$3:G3)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
F3:K4,H4:K10,G5:G10,F5:F9	Expression	=DAY(F3)>8	text	NO
F43:H49	Expression	=AND(DAY(F43)>=1,DAY(F43)<=15)	text	NO
F35:L35,F36:K36,F37:F41,G37:K42	Expression	=AND(DAY(F35)>=1,DAY(F35)<=15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:L2	Any value
H43:H50	Any value
I43:L50	Any value
F2	Any value
F3:F9	Any value
F10	Any value
F1	Any value
G1	Any value

. i had to add some additional lines to the original and am having some difficulty adapting the formulas to work with it. can you walk me through the modifications that would be needed to make you formulas work with the new ranges

 

[StephenCrump]

Only a very small change on Post #4:

	F	G	H	I	J	K	L
1	Nov-24
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34		jeff					Pat
35	24	25	26	27	28	29	30
36		Ramy	Josh	Aldo	Jeff	Justin	Carl
37		Carl	Israel	Gerardo	Brandun	Kedrin	Israel
38		Ciera	Angel	Brayan	Tre	Que	Aldo
39			Tuzzo	Dylan	Chris	Jada
40			Alex		Peter	Tiona	Miguel
41							Tevin
42							Pat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO

 

[jbrown021286]

Only a very small change on Post #4:

	F	G	H	I	J	K	L
1	Nov-24
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34		jeff					Pat
35	24	25	26	27	28	29	30
36		Ramy	Josh	Aldo	Jeff	Justin	Carl
37		Carl	Israel	Gerardo	Brandun	Kedrin	Israel
38		Ciera	Angel	Brayan	Tre	Que	Aldo
39			Tuzzo	Dylan	Chris	Jada
40			Alex		Peter	Tiona	Miguel
41							Tevin
42							Pat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO

not quite sure what i did wrong to cause it not to work

danny copy shop calendar test.xlsx
	F	G	H	I	J	K	L
1	November 2024
2	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	27	28	29	30	31	1	2
4		Ramy	Josh	Aldo	Jeff	Justin	Carl
5		Carl	Israel	Gerardo	Brandun	Kedrin	Tuzzo
6		Ciera	Angel	Brayan	Tre	Que	Angel
7			Tuzzo	Dylan	Chris	Jada
8			Alex		Peter	Tiona	Miguel
9							Tevin
10							Pat
11	3	4	5	6	7	8	9
12		Ramy	Josh	Aldo	Jeff	Justin	Justin
13		Carl	Israel	Gerardo	Brandun	Kedrin	Venro
14		Ciera	Angel	Brayan	Tre	Que	Bryan
15			Tuzzo	Dylan	Chris	Jada
16			Alex		Peter	Tiona	Miguel
17							Tevin
18							Pat
19	10	11	12	13	14	15	16
20		Ramy	Josh	Aldo	Jeff	Justin	Jeff
21		Carl	Israel	Gerardo	Brandun	Kedrin	Brandun
22		Ciera	Angel	Brayan	Tre	Que
23			Tuzzo	Dylan	Chris	Jada
24			Alex		Peter	Tiona	Miguel
25							Tevin
26							Pat
27	17	18	19	20	21	22	23
28		Ramy	Josh	Aldo	Jeff	Justin	Jeff
29		Carl	Israel	Gerardo	Brandun	Kedrin	Ramy
30		Ciera	Angel	Brayan	Tre	Que	Angel
31			Tuzzo	Dylan	Chris	Jada
32			Alex		Peter	Tiona	Miguel
33							Tevin
34							Pat
35	24	25	26	27	28	29	30
36		Ramy	Josh	Aldo	Jeff	Justin	Carl
37		Carl	Israel	Gerardo	Brandun	Kedrin	Israel
38		Ciera	Angel	Brayan	Tre	Que	Aldo
39			Tuzzo	Dylan	Chris	Jada
40			Alex		Peter	Tiona	Miguel
41							Tevin
42							Pat
43	1	2	Notes
44
45
46
47
48
49
50
November

Cell Formulas
Range	Formula
G2:L2	G2	=UPPER(TEXT(G3,"dddd"))
F3:L3	G3	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+1
G4:K10	G4	=teams!B23
L4,L36,L28,L20,L12	L4	=IF(M4=teams!$B$13,teams!$B$14,IF(M4=teams!$C$13,teams!$C$14,IF(M4=teams!$D$13,teams!$D$14,"")))
F1	F1	="November "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11	F11	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+8
G12:K18	G12	=teams!B23
L13,L37,L29,L21	L13	=IF(M13=teams!$B$5,teams!$B$6,IF(M13=teams!$C$5,teams!$C$6,IF(M13=teams!$D$5,teams!$D$6,IF(M13=teams!$E$5,teams!$E$6,IF(M13=teams!$F$5,teams!$F$6,"")))))
L14,L38,L30,L22	L14	=IF(M13=teams!$B$5,teams!$B$7,IF(M13=teams!$C$5,teams!$C$7,IF(M13=teams!$D$5,teams!$D$7,IF(M13=teams!$E$5,teams!$E$7,IF(M13=teams!$F$5,teams!$F$7,"")))))
F19:L19	F19	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+15
G20:K26	G20	=teams!B23
F27:L27	F27	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+22
G28:K34	G28	=teams!B23
F35:L35	F35	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+29
G36:K42	G36	=teams!B23
F43:G43	F43	=DaysAndWeeks+DATE(CalendarYear,11,1)-WEEKDAY(DATE(CalendarYear,11,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
CalendarYear	=January!$O$3	F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F1
WeekStart	=January!$O$10	F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(B$3:B1048575)-1,N),ISODD(f/N)*ISNUMBER(MATCH(B1048575,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K42	Expression	=LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G1048576)-1,N),ISEVEN(f/N)*ISNUMBER(MATCH(G1048576,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K34	Expression	=LET(N,8,Sat,$L$3:$L$34,f,FLOOR(ROWS(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
F3:K4,H4:K10,G5:G10,F5:F9	Expression	=DAY(F3)>8	text	NO
F43:H49	Expression	=AND(DAY(F43)>=1,DAY(F43)<=15)	text	NO
F35:L35,F36:K36,F37:F41,G37:K42	Expression	=AND(DAY(F35)>=1,DAY(F35)<=15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:L2	Any value
H43:H50	Any value
I43:L50	Any value
F2	Any value
F3:F9	Any value
F10	Any value
F1	Any value
G1	Any value

 

[StephenCrump]

My G3: =LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))

Your G3: =LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(B$3:B1048575)-1,N),ISODD(f/N)*ISNUMBER(MATCH(B1048575,OFFSET(INDEX(Sat,1+f),,,N),)))

So it looks like you copied the conditional formatting rule from another cell, and the relative row and column references have changed.

You shouldn't need to be copying. Just select the range G3:K42, select Conditional Formatting/New Rule and use my formula. (After you have cleared the dud formulae that are not working).

 

[jbrown021286]

My G3: =LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(G$3:G3)-1,N),ISODD(f/N)*ISNUMBER(MATCH(G3,OFFSET(INDEX(Sat,1+f),,,N),)))

Your G3: =LET(N,8,Sat,$L$3:$L$42,f,FLOOR(ROWS(B$3:B1048575)-1,N),ISODD(f/N)*ISNUMBER(MATCH(B1048575,OFFSET(INDEX(Sat,1+f),,,N),)))

So it looks like you copied the conditional formatting rule from another cell, and the relative row and column references have changed.

You shouldn't need to be copying. Just select the range G3:K42, select Conditional Formatting/New Rule and use my formula. (After you have cleared the dud formulae that are not working).

that got it thanks for the help

 

[StephenCrump]

Excellent, I'm glad it's working for you.