Ensuring 3 cells do NOT match

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm back with another problem :-)
I work a crew schedule; and I have 3 people who cannot work a shift
together. I need something that will NOT allow me to put them on a shift
together. We identify our shifts by D, S, or M (days, swings, mids) So I
cannot have Buck, Earle, or Small working a shift together. (weird rules
about these three)
I can't use conditional formatting for this because I already have used all
3 conditions for another issue.

Is there a way I can "raise a flag" if I schedule 2 or more of them on the
same shift?

Here's an idea of my schedule
Earle is in C7, Buck is in C27, and Small is in C51. If I were putting day
shift together in column D, then D7, D27, and/or D51 cannot match.

Any ideas O' Smart Excel people?
 
If the shift data is in A1 thru A3 then:

=MAX(COUNTIF(A1:A3,"D"),COUNTIF(A1:A3,"S"),COUNTIF(A1:A3,"M"))

will return 2 or 3 if you have a problem

alternatively

=(A1=A2)+(A2=A3)+(A1=A3)

will return non-zero if you have a problem
 
Thanks for your help, but that won't work. I already have the counters (both
of them) in use, to ensure I have three on shift (countif) and to ensure I
have all three positions filled (it's a pretty indepth spreadsheet)

I need to focus on these three people only.
What about a conditional format in the cells containing their names (C7 for
instance) (there is no conditional formatting there already) Can I fix it so
that if cells D7:AH7 equal D27:AH27 or D51:AH51, then cell C7 (contains
Earle's name) is highlighted?
I haven't figured out the conditional formatting other than the very basics.
 
Thanks Martin, the file looked hopeful, but I have problems with it.
I downloaded but when I opened the file I got a "Run time error 9"
"Subscript out of range"
 
I'm afraid I can't help you there, other than to say it works fine for me.
Bob is contactable through his website and also through the thousands
of his posts that you will find all over these newsgroups.

It's also very possible he may stop by this post before too long.

From the info you have given I think it will be the best solution.

Good Luck
Martin
 
do you have three conditional format on all cells.
one way to do it would be to add a Flag row.
in the cell in the flag row, you could use colorlike red or a symbol to
indicate something needs attention, such as not enough people scheduled.
too many people scheduled or the wrong group scheduled
use yellow for a warning. sombody scheduled for to many hours or too few etc
and green as everything OK
you vould expand it to have a numerical code to indicate which items are
cauing the light.
 
You can use data validation if you want to something like this...
Highlight Cell C7.
Click Data|Validation -> Custom
Add the formula
=NOT(OR(C7=C27, C7=C51))

Do the same for Cells C27 and C51 changing the appropriate cell references
to the formula. You may also want to change the error alert...
 
Thanks Jim, I'm getting closer!!
This works with one small problem. I only get the error alert when I
schedule them all three on the same shift. I also need it if I accidentally
schedule only 2 of them (THAT is a more realistic scenario)

Any way I can 'tweak' it to fit??
 
Thanks BJ, yes I have the flag row at the bottom of the schedule. I use it
to ensure I have 3 people on every crew, one qualified person per position.
As I said, it's a complicated schedule, and these three are not certified to
work a shift together.
They are all normally on different crews, but sometimes we have to temporary
fill someone (illness for example) with a body from another crew.

The flag row is so far down on the schedule that it could easily be missed.
 
The validation I gave you ensures that the value in C7 id not repeated in C27
or C51. That validation can be copied to other cells so I would assume that
that will take care of the multiple shifts issue??? I have no idea what your
sheet looks like so I am at a bit of a loss in how to get you to the next
step...
 
C7 contains the name (column C lists all the people.) D7-AH7 contains dates
during the month for Earle.

I adapted your formula into cel D7 (that is 1 Nov) and plan to copy it to
all the other cells in the same row for each individual. The NAMES won't
change in column C, but the shifts are placed in D-AH columns (D7 is the 1st,
E7 is the 2nd, F7 is the 3rd, etc... for Earle)
 
Using the validation concept from jim thompson
select D7:AH7
data-validation-custom
=not(And(D7=$C7,or(D27=$C27,D51=$C51)))
select D27:AH27
validation formula
=not(And(D27=$C27,or(D7=$C7,D51=$C51)))
and for D51:AH51
=not(And(D51=$C51,or(D27=$C27,D7=$C7)))
 
Back
Top