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

J

jbrown021286

Board Regular
Joined
Mar 13, 2023
Messages
85
Office Version
  1. 365
Platform
  1. Windows
danny copy shop calendar test.xlsx
ABCDEFG
1
2Shop Schedule
3
4Jeff's TeamJeffreyOpenThursday
5JoshOpenMonday
6TuzzoCloseTuesday
7RamyOpenMonday
8AldoCloseWednesday
9Justin's TeamBrandunOpenThursday
10IsraelCloseTuesday
11GerardoOpenWednesday
12JustinCloseFriday
13VenroCloseMonday
14Carl's TeamAngelCloseTuesday
15KedrinOpenFriday
16TreCloseThursday
17BrayanOpenWednesday
18CarlOpenMonday
19Used carsMiguelCloseSaturday
20
21TevinCloseSaturady
22PatCloseSaturday
23ExpressQueOpenFriday
24DylanCloseWednesday
25CieraCloseMonday
26JadaOpenFriday
27AlexCloseTuesday
28ChrisCloseThursday
29TionaCloseFriday
30PeterCloseThursday
31
32
33
34
35
36
37
38
39
Sheet1

danny copy shop calendar test.xlsx
ABCDEFG
1Shop ScheduleDecember 2024
2SUNDAYMONDAY
3Team 1JeffreyOpenThursday12
4JoshOpenMondayRamy
5TuzzoCloseTuesdayCarl
6RamyOpenMondayCiera
7AldoCloseWednesdayVenro
8Team 2BrandunOpenThursday
9IsraelCloseTuesday
10GerardoOpenWednesday
11JustinCloseFriday89
12Team 3AngelCloseTuesdayRamy
13KedrinOpenFridayCarl
14TreCloseThursdayCiera
15BrayanOpenWednesdayVenro
16CarlOpenMonday
17Used carsMiguelCloseSaturday
18VenroClose Saturday
19TevinCloseSaturady1516
20PatCloseSaturdayRamy
21ExpressQueOpenFridayCarl
22DylanCloseWednesdayCiera
23CieraCloseMondayVenro
24JadaOpenFriday
25AlexCloseTuesday
26ChrisCloseThursday
27TionaCloseFriday2223
28PeterCloseThursdayRamy
29Carl
30Ciera
31Venro
32
33
34
352930
36Ramy
37Carl
38Ciera
39Venro
December
Cell Formulas
RangeFormula
F1F1="December "&CalendarYear
F2F2=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F3:G3F3=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
F11:G11F11=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
F19:G19F19=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
F27:G27F27=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
F35:G35F35=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G2G2=UPPER(TEXT(G3,"dddd"))
G4:G10G4=teams!B23
G12:G18G12=teams!B23
G20:G26G20=teams!B23
G28:G34G28=teams!B23
G36:G39G36=teams!B23
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
CalendarYear=January!$O$3F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F1
WeekStart=January!$O$10F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:K42Expression=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),)))textNO
G3:K42Expression=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),)))textNO
G3:K34Expression=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),)))textNO
G3:K10Expression=DAY(G3)>8textNO
G35:L35,G36:K42Expression=AND(DAY(G35)>=1,DAY(G35)<=15)textNO
F3:F9Expression=DAY(F3)>8textNO
F35:F41Expression=AND(DAY(F35)>=1,DAY(F35)<=15)textNO
Cells with Data Validation
CellAllowCriteria
G2:L2Any value
F2Any value
F3:F9Any value
F10Any value
E1Any value
F1Any value
G1Any 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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
jbrown021286 said:
danny copy shop calendar test.xlsx
ABCDEFG
1
2Shop Schedule
3
4Jeff's TeamJeffreyOpenThursday
5JoshOpenMonday
6TuzzoCloseTuesday
7RamyOpenMonday
8AldoCloseWednesday
9Justin's TeamBrandunOpenThursday
10IsraelCloseTuesday
11GerardoOpenWednesday
12JustinCloseFriday
13VenroCloseMonday
14Carl's TeamAngelCloseTuesday
15KedrinOpenFriday
16TreCloseThursday
17BrayanOpenWednesday
18CarlOpenMonday
19Used carsMiguelCloseSaturday
20
21TevinCloseSaturady
22PatCloseSaturday
23ExpressQueOpenFriday
24DylanCloseWednesday
25CieraCloseMonday
26JadaOpenFriday
27AlexCloseTuesday
28ChrisCloseThursday
29TionaCloseFriday
30PeterCloseThursday
31
32
33
34
35
36
37
38
39
Sheet1

danny copy shop calendar test.xlsx
ABCDEFG
1Shop ScheduleDecember 2024
2SUNDAYMONDAY
3Team 1JeffreyOpenThursday12
4JoshOpenMondayRamy
5TuzzoCloseTuesdayCarl
6RamyOpenMondayCiera
7AldoCloseWednesdayVenro
8Team 2BrandunOpenThursday
9IsraelCloseTuesday
10GerardoOpenWednesday
11JustinCloseFriday89
12Team 3AngelCloseTuesdayRamy
13KedrinOpenFridayCarl
14TreCloseThursdayCiera
15BrayanOpenWednesdayVenro
16CarlOpenMonday
17Used carsMiguelCloseSaturday
18VenroClose Saturday
19TevinCloseSaturady1516
20PatCloseSaturdayRamy
21ExpressQueOpenFridayCarl
22DylanCloseWednesdayCiera
23CieraCloseMondayVenro
24JadaOpenFriday
25AlexCloseTuesday
26ChrisCloseThursday
27TionaCloseFriday2223
28PeterCloseThursdayRamy
29Carl
30Ciera
31Venro
32
33
34
352930
36Ramy
37Carl
38Ciera
39Venro
December
Cell Formulas
RangeFormula
F1F1="December "&CalendarYear
F2F2=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F3:G3F3=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
F11:G11F11=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
F19:G19F19=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
F27:G27F27=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
F35:G35F35=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G2G2=UPPER(TEXT(G3,"dddd"))
G4:G10G4=teams!B23
G12:G18G12=teams!B23
G20:G26G20=teams!B23
G28:G34G28=teams!B23
G36:G39G36=teams!B23
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
CalendarYear=January!$O$3F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F1
WeekStart=January!$O$10F3:G3, F11:G11, F19:G19, F27:G27, F35:G35, F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:K42Expression=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),)))textNO
G3:K42Expression=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),)))textNO
G3:K34Expression=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),)))textNO
G3:K10Expression=DAY(G3)>8textNO
G35:L35,G36:K42Expression=AND(DAY(G35)>=1,DAY(G35)<=15)textNO
F3:F9Expression=DAY(F3)>8textNO
F35:F41Expression=AND(DAY(F35)>=1,DAY(F35)<=15)textNO
Cells with Data Validation
CellAllowCriteria
G2:L2Any value
F2Any value
F3:F9Any value
F10Any value
E1Any value
F1Any value
G1Any 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
Click to expand...
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?
 
Upvote 0
danny copy shop calendar test.xlsx
ABCDEFGHIJKLM
1Shop ScheduleDecember 2024
2SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Team 1JeffreyOpenThursday1234567
4JoshOpenMondayRamyJoshAldoJeffJustinJustin2
5TuzzoCloseTuesdayCarlIsraelGerardoBrandunKedrinKedrin4
6RamyOpenMondayCieraAngelBryanTreQueJosh
7AldoCloseWednesdayVenroTuzzoDylanChrisJada
8Team 2BrandunOpenThursday Alex PeterTionaMiguel
9IsraelCloseTuesday Tevin
10GerardoOpenWednesday Pat
11JustinCloseFriday891011121314
12Team 3AngelCloseTuesdayRamyJoshAldoJeffJustinCarl3
13KedrinOpenFridayCarlIsraelGerardoBrandunKedrinVenro5
14TreCloseThursdayCieraAngelBryanTreQueBryan
15BrayanOpenWednesdayVenroTuzzoDylanChrisJada
16CarlOpenMonday Alex PeterTionaMiguel
17Used carsMiguelCloseSaturday Tevin
18VenroClose Saturday Pat
19TevinCloseSaturady15161718192021
20PatCloseSaturdayRamyJoshAldoJeffJustinJeff1
21ExpressQueOpenFridayCarlIsraelGerardoBrandunKedrinBrandun1
22DylanCloseWednesdayCieraAngelBryanTreQue
23CieraCloseMondayVenroTuzzoDylanChrisJada
24JadaOpenFriday Alex PeterTionaMiguel
25AlexCloseTuesday Tevin
26ChrisCloseThursday Pat
27TionaCloseFriday22232425262728
28PeterCloseThursdayRamyJoshAldoJeffJustinJustin2
29CarlIsraelGerardoBrandunKedrinRamy2
30CieraAngelBryanTreQueAngel
31VenroTuzzoDylanChrisJada
32 Alex PeterTionaMiguel
33 Tevin
34 Pat
352930311234
36RamyJoshAldoJeffJustin 
37CarlIsraelGerardoBrandunKedrin 
38CieraAngelBryanTreQue 
39VenroTuzzoDylanChrisJada
40 Alex PeterTionaMiguel
41 Tevin
42 Pat
4356Notes
44
45
46
47
48
49
50
December
Cell Formulas
RangeFormula
G2:L2G2=UPPER(TEXT(G3,"dddd"))
F3:L3G3=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
G4:K10G4=teams!B23
L4,L36,L28,L20,L12L4=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,L13L5=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,L14L6=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,"")))))
F1F1="December "&CalendarYear
F2F2=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11F11=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
G12:K18G12=teams!B23
F19:L19F19=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
G20:K26G20=teams!B23
F27:L27F27=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
G28:K34G28=teams!B23
F35:L35F35=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G36:K42G36=teams!B23
F43:G43F43=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
CalendarYear=January!$O$3F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F1
WeekStart=January!$O$10F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:K42Expression=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),)))textNO
G3:K42Expression=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),)))textNO
G3:K34Expression=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),)))textNO
G3:K10Expression=DAY(G3)>8textNO
G35:L35,G36:K42Expression=AND(DAY(G35)>=1,DAY(G35)<=15)textNO
F3:F9Expression=DAY(F3)>8textNO
F43:H49Expression=AND(DAY(F43)>=1,DAY(F43)<=15)textNO
F35:F41Expression=AND(DAY(F35)>=1,DAY(F35)<=15)textNO
Cells with Data Validation
CellAllowCriteria
G2:L2Any value
H43:H50Any value
I43:L50Any value
F2Any value
F3:F9Any value
F10Any value
E1Any value
F1Any value
G1Any value

danny copy shop calendar test.xlsx
ABCDEFGHIJKLMNOP
1
2Shop Schedule
3
4Jeff's TeamJeffreyOpenThursday
5JoshOpenMonday
6TuzzoCloseTuesday
7RamyOpenMonday
8AldoCloseWednesday
9Justin's TeamBrandunOpenThursday
10IsraelCloseTuesday
11GerardoOpenWednesday
12JustinCloseFriday
13VenroCloseMonday
14Carl's TeamAngelCloseTuesday
15KedrinOpenFriday
16TreCloseThursday
17BrayanOpenWednesday
18CarlOpenMonday
19Used carsMiguelCloseSaturday
20
21TevinCloseSaturady
22PatCloseSaturday
23ExpressQueOpenFriday
24DylanCloseWednesday
25CieraCloseMonday
26JadaOpenFriday
27AlexCloseTuesday
28ChrisCloseThursday
29TionaCloseFriday
30PeterCloseThursday
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.
 
Upvote 0
jbrown021286 said:
danny copy shop calendar test.xlsx
ABCDEFGHIJKLM
1Shop ScheduleDecember 2024
2SUNDAYMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Team 1JeffreyOpenThursday1234567
4JoshOpenMondayRamyJoshAldoJeffJustinJustin2
5TuzzoCloseTuesdayCarlIsraelGerardoBrandunKedrinKedrin4
6RamyOpenMondayCieraAngelBryanTreQueJosh
7AldoCloseWednesdayVenroTuzzoDylanChrisJada
8Team 2BrandunOpenThursday Alex PeterTionaMiguel
9IsraelCloseTuesday Tevin
10GerardoOpenWednesday Pat
11JustinCloseFriday891011121314
12Team 3AngelCloseTuesdayRamyJoshAldoJeffJustinCarl3
13KedrinOpenFridayCarlIsraelGerardoBrandunKedrinVenro5
14TreCloseThursdayCieraAngelBryanTreQueBryan
15BrayanOpenWednesdayVenroTuzzoDylanChrisJada
16CarlOpenMonday Alex PeterTionaMiguel
17Used carsMiguelCloseSaturday Tevin
18VenroClose Saturday Pat
19TevinCloseSaturady15161718192021
20PatCloseSaturdayRamyJoshAldoJeffJustinJeff1
21ExpressQueOpenFridayCarlIsraelGerardoBrandunKedrinBrandun1
22DylanCloseWednesdayCieraAngelBryanTreQue
23CieraCloseMondayVenroTuzzoDylanChrisJada
24JadaOpenFriday Alex PeterTionaMiguel
25AlexCloseTuesday Tevin
26ChrisCloseThursday Pat
27TionaCloseFriday22232425262728
28PeterCloseThursdayRamyJoshAldoJeffJustinJustin2
29CarlIsraelGerardoBrandunKedrinRamy2
30CieraAngelBryanTreQueAngel
31VenroTuzzoDylanChrisJada
32 Alex PeterTionaMiguel
33 Tevin
34 Pat
352930311234
36RamyJoshAldoJeffJustin 
37CarlIsraelGerardoBrandunKedrin 
38CieraAngelBryanTreQue 
39VenroTuzzoDylanChrisJada
40 Alex PeterTionaMiguel
41 Tevin
42 Pat
4356Notes
44
45
46
47
48
49
50
December
Cell Formulas
RangeFormula
G2:L2G2=UPPER(TEXT(G3,"dddd"))
F3:L3G3=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+1
G4:K10G4=teams!B23
L4,L36,L28,L20,L12L4=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,L13L5=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,L14L6=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,"")))))
F1F1="December "&CalendarYear
F2F2=IF(WeekStart="SUNDAY", "SUNDAY","MONDAY")
F11:L11F11=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+8
G12:K18G12=teams!B23
F19:L19F19=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+15
G20:K26G20=teams!B23
F27:L27F27=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+22
G28:K34G28=teams!B23
F35:L35F35=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+29
G36:K42G36=teams!B23
F43:G43F43=DaysAndWeeks+DATE(CalendarYear,12,1)-WEEKDAY(DATE(CalendarYear,12,1),(WeekStart="Monday")+1)+36
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
CalendarYear=January!$O$3F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F1
WeekStart=January!$O$10F3:L3, F11:L11, F19:L19, F27:L27, F35:L35, F43:G43, F2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G3:K42Expression=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),)))textNO
G3:K42Expression=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),)))textNO
G3:K34Expression=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),)))textNO
G3:K10Expression=DAY(G3)>8textNO
G35:L35,G36:K42Expression=AND(DAY(G35)>=1,DAY(G35)<=15)textNO
F3:F9Expression=DAY(F3)>8textNO
F43:H49Expression=AND(DAY(F43)>=1,DAY(F43)<=15)textNO
F35:F41Expression=AND(DAY(F35)>=1,DAY(F35)<=15)textNO
Cells with Data Validation
CellAllowCriteria
G2:L2Any value
H43:H50Any value
I43:L50Any value
F2Any value
F3:F9Any value
F10Any value
E1Any value
F1Any value
G1Any value

danny copy shop calendar test.xlsx
ABCDEFGHIJKLMNOP
1
2Shop Schedule
3
4Jeff's TeamJeffreyOpenThursday
5JoshOpenMonday
6TuzzoCloseTuesday
7RamyOpenMonday
8AldoCloseWednesday
9Justin's TeamBrandunOpenThursday
10IsraelCloseTuesday
11GerardoOpenWednesday
12JustinCloseFriday
13VenroCloseMonday
14Carl's TeamAngelCloseTuesday
15KedrinOpenFriday
16TreCloseThursday
17BrayanOpenWednesday
18CarlOpenMonday
19Used carsMiguelCloseSaturday
20
21TevinCloseSaturady
22PatCloseSaturday
23ExpressQueOpenFriday
24DylanCloseWednesday
25CieraCloseMonday
26JadaOpenFriday
27AlexCloseTuesday
28ChrisCloseThursday
29TionaCloseFriday
30PeterCloseThursday
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.
Click to expand...
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.
 

Attachments

  • Calendar.jpg
    Calendar.jpg
    13.1 KB · Views: 3
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
filling cells with using values in helper cell
Replies
0
Views
214
jbrown021286
J
J
  • Solved
conditional formatting based cells if there are duplicate cells in a range
Replies
9
Views
160
StephenCrump
S
K
  • Solved
Create macro that automatically goes to current date from a monthly calendar on each tab when file is opened
Replies
3
Views
1K
KimC33
K
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
258
Joe4
Joe4

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top