Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scheduleing Employee Twice
Thats perfect! Job well done! Thanks Joel.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
employee | Excel Discussion (Misc queries) | |||
Employee list | New Users to Excel | |||
Calculate employee hours for employee evaluation? | Excel Worksheet Functions | |||
Fromula to take employee # out of employee name field | Excel Discussion (Misc queries) | |||
Template for scheduleing office supply purchases | Excel Discussion (Misc queries) |