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.

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

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

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