Scheduleing Employee Twice
 

I just recently posted with a question about a formula to not be able to
schedule an Employee if they requested a day off, and it worked great. Thanks
to T. Valko.

They gave me the formula =MATCH(A3,B$5:B$23,0) to assist on my question.

I was wondering if I need to use the same basic formula for my next problem
area. I have tried using it, but to no prevail.

Question:
In cell B4:B23 I have a list of all the Employees for that DAY SHIFT and
what times they come in, as well the position they work. (a drop down menu is
in use to select employee names)
In cell H4:H23 I also have a list of all the employees for that NIGHT
SHIFT what times they come in, as well as position they work. (a drop down
menu is in use to select employee names)

I would like to know if there is a way to prevent a user from scheduleing
the same employee twice in a day in two different positions. We have had
problems in the past where the Manager scheduled an employee twice in the
same day, and we were left short handed. Of course we have some employees
work between shifts so they are oviously going to be on both shifts; as well
as splits. (no problem there is it shows that we have an employee scheduled
twice in that sense).

Scheduleing Employee Twice
 

why don't you use a condition format in the drop down box that turnes red if
the two drop down boxes are equal.

If the drop down box is in row 1 then put the following in both boxes
Use "formula is"
=(B1=H1)

"Matlock" wrote:

I just recently posted with a question about a formula to not be able to
schedule an Employee if they requested a day off, and it worked great. Thanks
to T. Valko.

They gave me the formula =MATCH(A3,B$5:B$23,0) to assist on my question.

I was wondering if I need to use the same basic formula for my next problem
area. I have tried using it, but to no prevail.

Question:
In cell B4:B23 I have a list of all the Employees for that DAY SHIFT and
what times they come in, as well the position they work. (a drop down menu is
in use to select employee names)
In cell H4:H23 I also have a list of all the employees for that NIGHT
SHIFT what times they come in, as well as position they work. (a drop down
menu is in use to select employee names)

I would like to know if there is a way to prevent a user from scheduleing
the same employee twice in a day in two different positions. We have had
problems in the past where the Manager scheduled an employee twice in the
same day, and we were left short handed. Of course we have some employees
work between shifts so they are oviously going to be on both shifts; as well
as splits. (no problem there is it shows that we have an employee scheduled
twice in that sense).

Scheduleing Employee Twice
 

Not sure if I am doing it right.

When I open a Conditional Format and imput =(B4=H4) in Cell B5, it
automaticly converts it to whatever format I set, even if I put a name in the
cell B4 or not.

Scheduleing Employee Twice
 

Make sure you set a Pattern Color in the conditional formating. Blanks in
both cell will also cause a matching condition. Is B4 and H4 the drop down
boxes?

"Matlock" wrote:

Not sure if I am doing it right.

When I open a Conditional Format and imput =(B4=H4) in Cell B5, it
automaticly converts it to whatever format I set, even if I put a name in the
cell B4 or not.

Scheduleing Employee Twice
 

I set the Format Condition for Blue when they are TRUE.

For Monday B4 through B23 are Dropdown Menus.
20 Total, 1 for each position. For Dayshift.
Same for Night which is H4 through H23.

Scheduleing Employee Twice
 

Not True. True would mean they both have any data. It is when the two cells
havve the same value.

=(B4=H4)

If B4 equal s H4 then you get a true condition. If they are unequal you
will get a False condition.

You could set two conditional conditions. Blue when not equal and Red when
equal

=(B4=H4) Red
=(B4<H4) Blue

"Matlock" wrote:

I set the Format Condition for Blue when they are TRUE.

For Monday B4 through B23 are Dropdown Menus.
20 Total, 1 for each position. For Dayshift.
Same for Night which is H4 through H23.

Scheduleing Employee Twice
 

Awesome, I got the B4:B23 to be highlighted if H4:H23 have the same name.

Now, how do we get B4:B23 to highlight itself if the same employee is listed
twice within B4:B23?

Scheduleing Employee Twice
 

Use countif

=IF(COUNTIF(B$4:B$24,B4)1,TRUE,FALSE)

Notice the S. Copy this down to al cells between B4 to B24.

"Matlock" wrote:

Awesome, I got the B4:B23 to be highlighted if H4:H23 have the same name.

Now, how do we get B4:B23 to highlight itself if the same employee is listed
twice within B4:B23?

Scheduleing Employee Twice
 

Thats perfect! Job well done! Thanks Joel.