Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bob Wall
 
Posts: n/a
Default 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   Report Post  
Rob Hick
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting Error ddate Excel Worksheet Functions 0 May 5th 05 09:00 PM
difficulty with conditional formatting Deb Excel Discussion (Misc queries) 0 March 23rd 05 07:13 PM
conditional formatting question Deb Excel Discussion (Misc queries) 0 March 23rd 05 03:07 AM
Determine cells that drive conditional formatting? Nicolle K. Excel Discussion (Misc queries) 2 January 7th 05 02:08 AM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM


All times are GMT +1. The time now is 08:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"