automatically copying a group of cells if changes are made to the master copy

[jbrown021286]

danny copy shop calendar test.xlsx
	A	B	C	D	E	F	G
1
2		Shop Schedule
3
4		Jeff's Team	Jeffrey	Open	Thursday
5			Josh	Open	Monday
6			Tuzzo	Close	Tuesday
7			Ramy	Open	Monday
8			Aldo	Close	Wednesday
9		Justin's Team	Brandun	Open	Thursday
10			Israel	Close	Tuesday
11			Gerardo	Open	Wednesday
12			Justin	Close	Friday
13			Venro	Close	Monday
14		Carl's Team	Angel	Close	Tuesday
15			Kedrin	Open	Friday
16			Tre	Close	Thursday
17			Brayan	Open	Wednesday
18			Carl	Open	Monday
19		Used cars	Miguel	Close	Saturday
20
21			Tevin	Close	Saturady
22			Pat	Close	Saturday
23		Express	Que	Open	Friday
24			Dylan	Close	Wednesday
25			Ciera	Close	Monday
26			Jada	Open	Friday
27			Alex	Close	Tuesday
28			Chris	Close	Thursday
29			Tiona	Close	Friday
30			Peter	Close	Thursday
31
32
33
34
35
36
37
38
39
Sheet1

danny copy shop calendar test.xlsx
	A	B	C	D	E	F	G
1	Shop Schedule					December 2024
2						SUNDAY	MONDAY
3	Team 1	Jeffrey	Open	Thursday		1	2
4		Josh	Open	Monday			Ramy
5		Tuzzo	Close	Tuesday			Carl
6		Ramy	Open	Monday			Ciera
7		Aldo	Close	Wednesday			Venro
8	Team 2	Brandun	Open	Thursday
9		Israel	Close	Tuesday
10		Gerardo	Open	Wednesday
11		Justin	Close	Friday		8	9
12	Team 3	Angel	Close	Tuesday			Ramy
13		Kedrin	Open	Friday			Carl
14		Tre	Close	Thursday			Ciera
15		Brayan	Open	Wednesday			Venro
16		Carl	Open	Monday
17	Used cars	Miguel	Close	Saturday
18		Venro	Close	Saturday
19		Tevin	Close	Saturady		15	16
20		Pat	Close	Saturday			Ramy
21	Express	Que	Open	Friday			Carl
22		Dylan	Close	Wednesday			Ciera
23		Ciera	Close	Monday			Venro
24		Jada	Open	Friday
25		Alex	Close	Tuesday
26		Chris	Close	Thursday
27		Tiona	Close	Friday		22	23
28		Peter	Close	Thursday			Ramy
29							Carl
30							Ciera
31							Venro
32
33
34
35						29	30
36							Ramy
37							Carl
38							Ciera
39							Venro
December

Cell Formulas
Range	Formula
F1	F1	="December "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F3:G3	F3	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
F11:G11	F11	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
F19:G19	F19	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
F27:G27	F27	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
F35:G35	F35	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G2	G2	=UPPER(TEXT(G3,"dddd"))
G4:G10	G4	=teams!B23
G12:G18	G12	=teams!B23
G20:G26	G20	=teams!B23
G28:G34	G28	=teams!B23
G36:G39	G36	=teams!B23
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
CalendarYear	=January!$O$3	F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F1
WeekStart	=January!$O$10	F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, 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(G$3:G3)-1,N),ISEVEN(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),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(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K10	Expression	=DAY(G3)>8	text	NO
G35:L35,G36:K42	Expression	=AND(DAY(G35)>=1,DAY(G35)<=15)	text	NO
F3:F9	Expression	=DAY(F3)>8	text	NO
F35:F41	Expression	=AND(DAY(F35)>=1,DAY(F35)<=15)	text	NO

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

i have a calendar with a employee schedule next to each month. i was wanting to find a way to have a master schedule on a separate sheet that when any changes are mad to it is reflected in all the other ones in each month. the part that is tripping me up is that the number of people on each team may change and the total number of people may go up and down. so i am not sure how to achieve this. is ther a way to do this. any recommendations would be appreciated

 

[HighAndWilder]

danny copy shop calendar test.xlsx
	A	B	C	D	E	F	G
1
2		Shop Schedule
3
4		Jeff's Team	Jeffrey	Open	Thursday
5			Josh	Open	Monday
6			Tuzzo	Close	Tuesday
7			Ramy	Open	Monday
8			Aldo	Close	Wednesday
9		Justin's Team	Brandun	Open	Thursday
10			Israel	Close	Tuesday
11			Gerardo	Open	Wednesday
12			Justin	Close	Friday
13			Venro	Close	Monday
14		Carl's Team	Angel	Close	Tuesday
15			Kedrin	Open	Friday
16			Tre	Close	Thursday
17			Brayan	Open	Wednesday
18			Carl	Open	Monday
19		Used cars	Miguel	Close	Saturday
20
21			Tevin	Close	Saturady
22			Pat	Close	Saturday
23		Express	Que	Open	Friday
24			Dylan	Close	Wednesday
25			Ciera	Close	Monday
26			Jada	Open	Friday
27			Alex	Close	Tuesday
28			Chris	Close	Thursday
29			Tiona	Close	Friday
30			Peter	Close	Thursday
31
32
33
34
35
36
37
38
39
Sheet1

danny copy shop calendar test.xlsx
	A	B	C	D	E	F	G
1	Shop Schedule					December 2024
2						SUNDAY	MONDAY
3	Team 1	Jeffrey	Open	Thursday		1	2
4		Josh	Open	Monday			Ramy
5		Tuzzo	Close	Tuesday			Carl
6		Ramy	Open	Monday			Ciera
7		Aldo	Close	Wednesday			Venro
8	Team 2	Brandun	Open	Thursday
9		Israel	Close	Tuesday
10		Gerardo	Open	Wednesday
11		Justin	Close	Friday		8	9
12	Team 3	Angel	Close	Tuesday			Ramy
13		Kedrin	Open	Friday			Carl
14		Tre	Close	Thursday			Ciera
15		Brayan	Open	Wednesday			Venro
16		Carl	Open	Monday
17	Used cars	Miguel	Close	Saturday
18		Venro	Close	Saturday
19		Tevin	Close	Saturady		15	16
20		Pat	Close	Saturday			Ramy
21	Express	Que	Open	Friday			Carl
22		Dylan	Close	Wednesday			Ciera
23		Ciera	Close	Monday			Venro
24		Jada	Open	Friday
25		Alex	Close	Tuesday
26		Chris	Close	Thursday
27		Tiona	Close	Friday		22	23
28		Peter	Close	Thursday			Ramy
29							Carl
30							Ciera
31							Venro
32
33
34
35						29	30
36							Ramy
37							Carl
38							Ciera
39							Venro
December

Cell Formulas
Range	Formula
F1	F1	="December "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F3:G3	F3	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
F11:G11	F11	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
F19:G19	F19	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
F27:G27	F27	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
F35:G35	F35	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G2	G2	=UPPER(TEXT(G3,"dddd"))
G4:G10	G4	=teams!B23
G12:G18	G12	=teams!B23
G20:G26	G20	=teams!B23
G28:G34	G28	=teams!B23
G36:G39	G36	=teams!B23
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
CalendarYear	=January!$O$3	F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F1
WeekStart	=January!$O$10	F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, 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(G$3:G3)-1,N),ISEVEN(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),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(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K10	Expression	=DAY(G3)>8	text	NO
G35:L35,G36:K42	Expression	=AND(DAY(G35)>=1,DAY(G35)<=15)	text	NO
F3:F9	Expression	=DAY(F3)>8	text	NO
F35:F41	Expression	=AND(DAY(F35)>=1,DAY(F35)<=15)	text	NO

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

i have a calendar with a employee schedule next to each month. i was wanting to find a way to have a master schedule on a separate sheet that when any changes are mad to it is reflected in all the other ones in each month. the part that is tripping me up is that the number of people on each team may change and the total number of people may go up and down. so i am not sure how to achieve this. is ther a way to do this. any recommendations would be appreciated

Do you store the date on Sheet1?

I would have a dynamic calendar as you can only view one sheet at a time. You can change the month that the calendar needs to display in a number of different ways.

Do you need to display the team name on the calendar?

I would avoid copying data, Excel 365 has enough clever functions to display data from a single source in many different ways?

Can you use XL2BB to post a full calendar and the source data that is used on it?

 

[jbrown021286]

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

Cell Formulas
Range	Formula
G2:L2	G2	=UPPER(TEXT(G3,"dddd"))
F3:L3	G3	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,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,"")))
L5,L37,L29,L21,L13	L5	=IF(M5=teams!$B$5,teams!$B$6,IF(M5=teams!$C$5,teams!$C$6,IF(M5=teams!$D$5,teams!$D$6,IF(M5=teams!$E$5,teams!$E$6,IF(M5=teams!$F$5,teams!$F$6,"")))))
L6,L38,L30,L22,L14	L6	=IF(M5=teams!$B$5,teams!$B$7,IF(M5=teams!$C$5,teams!$C$7,IF(M5=teams!$D$5,teams!$D$7,IF(M5=teams!$E$5,teams!$E$7,IF(M5=teams!$F$5,teams!$F$7,"")))))
F1	F1	="December "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11	F11	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
G12:K18	G12	=teams!B23
F19:L19	F19	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
G20:K26	G20	=teams!B23
F27:L27	F27	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
G28:K34	G28	=teams!B23
F35:L35	F35	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G36:K42	G36	=teams!B23
F43:G43	F43	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,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(G$3:G3)-1,N),ISEVEN(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),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(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K10	Expression	=DAY(G3)>8	text	NO
G35:L35,G36:K42	Expression	=AND(DAY(G35)>=1,DAY(G35)<=15)	text	NO
F3:F9	Expression	=DAY(F3)>8	text	NO
F43:H49	Expression	=AND(DAY(F43)>=1,DAY(F43)<=15)	text	NO
F35:F41	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

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

it is a normal calendar spread sheet that i have added a few rows to and some formulas to allow for schedules to be changed easily. but all the months are laid out the same. the sheet named Sheet1 has what i want to me my master sheet to make changes to then those changes show up on the other months sheets. and the team names do need to be on the sheet.

 

[HighAndWilder]

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

Cell Formulas
Range	Formula
G2:L2	G2	=UPPER(TEXT(G3,"dddd"))
F3:L3	G3	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,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,"")))
L5,L37,L29,L21,L13	L5	=IF(M5=teams!$B$5,teams!$B$6,IF(M5=teams!$C$5,teams!$C$6,IF(M5=teams!$D$5,teams!$D$6,IF(M5=teams!$E$5,teams!$E$6,IF(M5=teams!$F$5,teams!$F$6,"")))))
L6,L38,L30,L22,L14	L6	=IF(M5=teams!$B$5,teams!$B$7,IF(M5=teams!$C$5,teams!$C$7,IF(M5=teams!$D$5,teams!$D$7,IF(M5=teams!$E$5,teams!$E$7,IF(M5=teams!$F$5,teams!$F$7,"")))))
F1	F1	="December "&CalendarYear
F2	F2	=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11	F11	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
G12:K18	G12	=teams!B23
F19:L19	F19	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
G20:K26	G20	=teams!B23
F27:L27	F27	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
G28:K34	G28	=teams!B23
F35:L35	F35	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G36:K42	G36	=teams!B23
F43:G43	F43	=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,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(G$3:G3)-1,N),ISEVEN(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),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(XFC$3:XFC1048552)-1,N),ISODD(f/N)*ISNUMBER(MATCH(XFC1048552,OFFSET(INDEX(Sat,1+f),,,N),)))	text	NO
G3:K10	Expression	=DAY(G3)>8	text	NO
G35:L35,G36:K42	Expression	=AND(DAY(G35)>=1,DAY(G35)<=15)	text	NO
F3:F9	Expression	=DAY(F3)>8	text	NO
F43:H49	Expression	=AND(DAY(F43)>=1,DAY(F43)<=15)	text	NO
F35:F41	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

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

it is a normal calendar spread sheet that i have added a few rows to and some formulas to allow for schedules to be changed easily. but all the months are laid out the same. the sheet named Sheet1 has what i want to me my master sheet to make changes to then those changes show up on the other months sheets. and the team names do need to be on the sheet.

I have some ideas but some questions first.

Where do you store the date on the Shop Schedule?

Do you have one Shop Schedule per month or just one schedule sheet that holds data for many months over time?

Do you delete the Shop Schedule data once the month has passed?

Do you rely on forenames or do you use a combination of forenames and surnames to identfy a person.

Can a person change teams?

The row height OR number of rows per date on the calendar will need to such that all likely data is accommodated. This may involve adjusting the number of rows or
row height dynamically. Both possible.

The attached image suggests a way in which the data on the calendar could be displayed. It is all in one cell. Your feedback please.
I'm looking for a way in which not to repeat the team names.

The idea that I am playing around with involves one Calendar sheet that can be used for any month. The month is selected and the data changes dynamically.
Maintaining multiple Calendars is unnecessary unless you have a good reason.

I will post a demo but I'm waiting for the above information.