Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formatting a Schedule
Excel 97
I have a schedule in which various operators are assigned in pairs to operate certain equipment. Because of safety issues, trainees must work with certified operators, so two trainees cannot be paired on the same machine on the same shift. My schedule has the employees listed down the first column, and the days of the week are in columns B:H. Operator qualifications (C for certified, T for trainee) are in a separate column off to the right. Machine assignments are listed in the columns by day to the right of the employees' names. Machines are designated by unique shop names such as North, Central, South etc. I'm looking for a way to format the cells containing the assignments (the ones with the machine designations) to flag if two trainees are inadvertently scheduled to the same machine on the same shift. I have a simplified example of the schedule below. I'm wondering if a SUMIF function combined with conditional formatting would do it, but I'm having trouble putting it together. Any help is much appreciated, as always. EMPL. MON TUE WED ... QUAL Adam North South C Betty South Central C Charlie North South T Donna South North T Eddie Central North T Fran Central Central T In this case above, the assigned machine "Central" for Eddie and Fran on Monday should meet the condition and be flagged in a particular format. Tuesday's assignments would flag both "North" assignments, etc. Again, any help would be appreciated and thanks in advance. |
#2
|
|||
|
|||
hi bob,
i would suggest that this can't be done hidden behind the scenes in a conditional formatting formual. You will need to create another table that 'checks' that two trainees aren't allocated to the same machine on a given day. You can then use the value returned in this table to conditionally format the results in your schedule table to highlight problems as required. i had a quick fiddle and got the following system working: using the data you suggested above, except that i put the 'QUAL' field next to the 'EMPL' field - it made more sense here, then you can create new days just by adding a column and copying the formulas. with the schedule table structured this way and occupying A1:D7, create a new 'check' table (either underneath or on another sheet - bear in mind that you can only conditionally format using data from the same sheet) like the following Machine mon tue etc... North ... ... South ... ... Central ... ... etc... then in the cells put the following formula (it is all one formula - broken up for display purposes!): =INDEX(($B$2:$B$7,C$2:C$7), MATCH($B10,C$2:C$7,FALSE),1) =INDEX((OFFSET($B$2,MATCH($B10,C$2:C$7,FALSE),0):$ B$7, OFFSET(C$2,MATCH($B10,C$2:C$7,FALSE),0):C$7), MATCH($B10,OFFSET(C$2,MATCH($B10,C$2:C$7,FALSE),0) :C$7, FALSE),1) The basic premise of the formula is to look up the qualification (colB) of the first instance of the machine in the day-column (eg mon=colA) and then check if this is equal to the qualification of the second instance of the machine in the day-column. I've used the offset function to only skip over the first instance and start from the cell below it to find the second instance. It was slightly complicated by making it 'copiable' so the range reference for the INDEX() function is combined of colB and a day-column. You can then use a relatively simple formula in the conditional formatting to look up the relevant cell in the 'check' table, for example: =VLOOKUP(C2,$B$10:$D$12,COLUMN()-1,FALSE) where C2 is the cell the formula is in, and B10:D12 is the 'check' table. hope that helps. Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |