Shift Pattern Dropdown List and auto populate calendar

[missja]

Hello Excel Experts,

I need some help on how can I make a dropdown list for "Shift" that will automatically populate the calendar per month.
I used the Shift Pattern template in excel. The pattern is in the tab Shift Pattern. I want to add the shift pattern in the tab Monthly Sched, I just put a monthly shift just for reference.
Please see the attached spreadsheet. Thank you for the help.

[JeteMc]

Hello missja and Welcome to Excel Forum.
To change the date using drop down(s) I would suggest using one for Month and one for Year as day can be assumed to be the 1st.
A list of months is placed in column AS and the corresponding month numbers are placed in column AT. The drop down for month is in cell E3.
A list of years is placed in column AU. The drop down for year is in cell F3.
The formula that populates cell B3 is: =DATE(F3,INDEX(AT2:AT13,MATCH(E3,AS2:AS13,0)),1)
The referencing for dates in row 7 is changed to read: =IF(SUM(J7,1)>EOMONTH($B3,0),"",SUM(J7,1))
The conditional formatting applied to distinguish weekends is: =OR(WEEKDAY(J7)=7,WEEKDAY(J7)=1)
The yellow/green cells are populated using: =IF(J$7="","",IF(WEEKDAY(J$7)=3,"O","D"))
If you need any other help please explain in detail.
Let us know if you have any questions.

[missja]

Hi JeteMC,

Thank you so much for your help. I need to have a dropdown list on the column "Shift" (I7) on the Monthly Sched Tab. I have a 7 Shift pattern in the tab "Shift Pattern"(this is from Excel Template). What I want to achieve is to have these patterns in the tab "Monthly Sched".
I hope you can help with this.

[chaiyya345]

Hi missja,
please find the attachment.
Hope this helps.

Thank-You very much

[missja]

Hi Chaiyya,

Thank you for the help. I need the shift pattern (the colors from the calendar) from the "Shift Pattern" tab to be reflected on the "Monthly Sched" tab.
If i choose shift A on the dropdown list you created, I need the exact pattern of Shift A from the "Shift Pattern" Tab in the "Monthly Sched" tab.
I hope you get what I mean. Thank you so much for your very kind help. I appreciate it.

[JeteMc]

I am not sure that I understand, however I am going to make a proposal based on what I feel you want.
Drop downs are placed in I8:I10, I20:I37 and I47:I48 on the Monthly Sched sheet with the source: ='Shifts Details'!$D$5:$J$5
Numbers 1:7 are placed in D6:J6 of the Shift Details sheet.
Columns J:AM are populated using: =IF(J$7="","",IF(WEEKDAY(J$7)=INDEX('Shifts Details'!$D$6:$J$6,MATCH($I8,'Shifts Details'!$D$5:$J$5,0)),"O","D"))
Let us know if you have any questions.

[missja]

Hi JeteMC,

You're brilliant! Thank you so much, I super appreciate your help.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[missja]

Dear Excel Experts,

I hope you can help me with this Shift Template.
I would like to have a dropdown list of the Locations AN7:AN12 in the Calendar F8:AJ28 instead of having "O" and "D".
I know that I can just put the data validation to have the dropdown list, but I need the "Shift pattern" E8 to be still linked to F8:AJ28.
So when I change the date, the data will also change. Please see the attached spreadsheet. Thank you so much for your help.

[AliGW]

Your duplicate thread has been closed and the new request copied to this thread.

Continue here, please.

[missja]

great. Thank you AliGW.

[JeteMc]

If I understand correctly then the following does what you want:
1. Modify the formula in G7:AJ7 to read: =IF(OR(F7="",SUM(F7,1)>EOMONTH($B3,0)),"",SUM(F7,1))
2. Modify the conditional formatting rule for green to the formula: =AND(F$7<>"",WEEKDAY(F$7)=INDEX('Shifts Details'!$D$6:$K$6,MATCH($E8,'Shifts Details'!$D$5:$K$5,0)))
3. Modify the conditional formatting rule for yellow to the formula: =F$7<>""
4. Remove the formula from F8:AJ28 and apply data validation to those cells.
Let us know if you have any questions.

[missja]

Hi JeteMC,

Thank you again for your kind help.
Yes you got it right, but I still need the "OFF" written on the green colours and make the Empty cells F9:AJ33 a "Light grey" color
I will set in the conditional formatting the cells (F9:AJ33) that have a position name with Day (Ex. Block B Access Control Day) as "Yellow" and Night (Ex. Block C Access Control Night) as "Medium Grey"

[missja]

Also, I cannot figure out how can i fix the day off.. Life for Shift A, the day off should always be Sunday. I want to restrict the user to put a location and anything on the cells of Day Off's which are in green colors.

[JeteMc]

As to putting "OFF" in the green cells, I don't know of a way to have conditional formatting put text in a cell, which doesn't mean that there isn't a way. My proposal is to add "OFF" to the drop-down list.
As to filling empty cells light grey I added another rule: =F8=""
As to the day and night, I added a Day/Night drop down in cell E3
The rule for yellow is: =AND($E$3="day",F8<>"")
The rule for dark gray is: =AND($E$3="night",F8<>"")
Not sure about solution to post #14, perhaps someone else will have an idea.
Let us know if you have any questions.

[missja]

Hi JeteMC,

Thank you for the effort to add Day/Night. But each Employee will have a combination of Day and Night shifts.
It would be great if the Day/Night dropdown will have the function to show only the Night Shift (so other cells that have Day Shift will temporary disappear)
and it will be the same when the Day on the dropdown is selected, the Night Shift in the other cells will temporary Disappear.
So we will have a dropdown Day/Night, Day, Night. and if possible, the total shift will appear to the E5.
Please see the attached spreadsheet.

[JeteMc]

Not sure that I understand what is meant by disappear.
If you could show us an example of what you would like to see when either day or night is selected, then it may help.
I did a little work to the file.
Moved the day off conditional formatting rule to the top and also replaced all of the "Night" rules with: =ISNUMBER(SEARCH("night",F9))
Let us know if you have any questions.

[missja]

Hi JeteMC,

The conditional formatting you added for Night is good, I added the same for Day also.
For the computation of total shift locations Night and Day duty, I added a dropdownlist on E2 Day and Night. Total Shift locations in E4.

Now I'm trying to make the dropdown list of locations like a dynamic filter, so it will be easier to add the location in the cell F9:AJ15.

I have the dymanic filter in J54. I don't know what's the error in the formula in P54 as when I type in N53, the result in P54 is error.

[JeteMc]

Formula in cell P54:=IFERROR(INDEX($G$54:$G$70,MATCH(ROW(M53),$O$54:$O$70,0)),"")
The reason that the IFERROR is triggered is that ROW(M53) returns the number 53 which doesn't match any of the numbers in O54:O70 which are 1:15.
I am not sure what is being attempted so that is all the help I can offer until we get more information of what should appear in P54:P70.
I will be away from my computer for a few days but will look at this again after that, provided another contributor hasn't already resolved the issue.

[missja]

Hi JeteMC,

No problem. I just want to have the drop list be dynamic so that when I type the location, it would be easier to search. for ex. if I type anywhere in (F9:AJ15) "CCTV" and press the arrow down it will only show all CCTV locations.