I have searched for this. I have tried to retrofit other conditional formatting threads. I thought I was close but couldn't get it to function. I figure this is easy and I am just on brain melt.
I am trying to make a dynamic shift calendar and when someone changes the operator who is on typically on schedule to someone who normally wouldn't be working the cell changes fill color.
Sorry for complex detail below but I hope it helps it has a lot of explanation.
I have 3 sheets that contain different information.
Sheet 1 is the calendar itself set to print. Sheet Title: Month
It has 6 weeks, with 4 separate rows for operators to be able to fill. Typically only use two rows. Days, Nights
[column C] has an A-P [Operator ID] for who typically works that shift for the whole week of 7 days. [A] works all 7 days, works the 7 - nights, each day of the week displays their proper name, [A] would display as Bob, displays as Joe. Calendar days [columns D-J] displays the Name of the operator using a vlookup using Y@G sheet table and Operator ID table [table3] and the equivalent column Column D for shift row 1 (days), column E for shift row 2 (nights)
If someone different works during that period, you simply add their [operator identifier] into the Y@G table for the desired date and shift. The calendar updates correctly.
and the replacement [operators name] is displayed.
So I need to conditional format the Days of week in the rows when someone other than the normal operator works the cell changes. I think the behavior is this: [Operator Name] looks up [Operator ID] in [table3] when it does not equal the value in column c of the same row. Highlight the cell yellow.
example tom is working Wed. Operator name [Tom] = operator ID [C] and column c value = Operator typically working = [Joe] since column c is expects to see [B: Joe] then the cell containing now [Tom] will highlight.
Sheet 2: is the calendar year at a glance: Title: Y@G it has a single table1 A1:I383
Columns are A: Month as dddd, B: date as mm/dd/yy, C: Day of the week as ddd, columns D - I are for operator ID entry.
as example column D - dayshift, E - Nights, F - Other, G - Other, H & I are possible future or for larger plants we have with more people.
You input the [operator id] - A,B,C,D.... in the desired cell for date and shift.
Sheet 3: I have table of recognized holidays with capacity for alternate days because our company shifts weekend holidays to weekday recognition.
Table for safety meetings for the year based on predetermined frequency. The capacity to add a second safety meeting per month.
Lastly is [Table3] which is plant operators. column 1 is A - P with the adjacent the named operator. I18:J34
I have a Dropbox link to my file if it helps: https://www.dropbox.com/s/3dyp3x8iurp4ucv/dynamic calendarv3 - Copy.xlsm?dl=0
I am trying to make a dynamic shift calendar and when someone changes the operator who is on typically on schedule to someone who normally wouldn't be working the cell changes fill color.
Sorry for complex detail below but I hope it helps it has a lot of explanation.
I have 3 sheets that contain different information.
Sheet 1 is the calendar itself set to print. Sheet Title: Month
It has 6 weeks, with 4 separate rows for operators to be able to fill. Typically only use two rows. Days, Nights
[column C] has an A-P [Operator ID] for who typically works that shift for the whole week of 7 days. [A] works all 7 days, works the 7 - nights, each day of the week displays their proper name, [A] would display as Bob, displays as Joe. Calendar days [columns D-J] displays the Name of the operator using a vlookup using Y@G sheet table and Operator ID table [table3] and the equivalent column Column D for shift row 1 (days), column E for shift row 2 (nights)
If someone different works during that period, you simply add their [operator identifier] into the Y@G table for the desired date and shift. The calendar updates correctly.
and the replacement [operators name] is displayed.
So I need to conditional format the Days of week in the rows when someone other than the normal operator works the cell changes. I think the behavior is this: [Operator Name] looks up [Operator ID] in [table3] when it does not equal the value in column c of the same row. Highlight the cell yellow.
example tom is working Wed. Operator name [Tom] = operator ID [C] and column c value = Operator typically working = [Joe] since column c is expects to see [B: Joe] then the cell containing now [Tom] will highlight.
Sheet 2: is the calendar year at a glance: Title: Y@G it has a single table1 A1:I383
Columns are A: Month as dddd, B: date as mm/dd/yy, C: Day of the week as ddd, columns D - I are for operator ID entry.
as example column D - dayshift, E - Nights, F - Other, G - Other, H & I are possible future or for larger plants we have with more people.
You input the [operator id] - A,B,C,D.... in the desired cell for date and shift.
Sheet 3: I have table of recognized holidays with capacity for alternate days because our company shifts weekend holidays to weekday recognition.
Table for safety meetings for the year based on predetermined frequency. The capacity to add a second safety meeting per month.
Lastly is [Table3] which is plant operators. column 1 is A - P with the adjacent the named operator. I18:J34
I have a Dropbox link to my file if it helps: https://www.dropbox.com/s/3dyp3x8iurp4ucv/dynamic calendarv3 - Copy.xlsm?dl=0
