Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 -- Gary''s Student - gsnu200743 "GIdunno" wrote: 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? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Sounds like a job for superman!
Or in this case Bob Phillips. Take a look at Bob's site here. http://www.xldynamic.com/source/xld.....Download.html HTH Martin "GIdunno" wrote in message ... 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? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200743 "GIdunno" wrote: 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? |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" "MartinW" wrote: Sounds like a job for superman! Or in this case Bob Phillips. Take a look at Bob's site here. http://www.xldynamic.com/source/xld.....Download.html HTH Martin "GIdunno" wrote in message ... 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? |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 "GIdunno" wrote in message ... 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" "MartinW" wrote: Sounds like a job for superman! Or in this case Bob Phillips. Take a look at Bob's site here. http://www.xldynamic.com/source/xld.....Download.html HTH Martin "GIdunno" wrote in message ... 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? |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "GIdunno" wrote: 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? |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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? |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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?? "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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? |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "bj" wrote: 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. "GIdunno" wrote: 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? |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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?? "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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? |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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) "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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?? "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "GIdunno" wrote: 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? |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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))) "GIdunno" wrote: 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. "bj" wrote: 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. "GIdunno" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
Look for a value in two cells and match it with another | Excel Discussion (Misc queries) | |||
Match cells with cells on another workbook | Excel Worksheet Functions | |||
Query counting cells in a row that exactly match cells in another | Excel Discussion (Misc queries) | |||
Ensuring deleted data cannot be recovered | Excel Discussion (Misc queries) |