formula help with text "yes, "no" already have formula just need it revised

[steve89]

hello i have this formula

=IFERROR(IF($BQ$24=13,IF($BL$26=0,"yes","no")&TEXT($BL$26/BL$26,),"NO"),"yes")

i want the formula to know the following because it works to an extent
if BL26 = 0 then "NO"
if BQ24 = 13 then "YES"


thank you

 

[dreid1011]

=IF(TEXTJOIN(", ",,IF(COUNTIF(D10:D52,'Employee Data'!$A$2:$A$29),"",IF('Employee Data'!$A$2:$A$29<>"",'Employee Data'!$A$2:$A$29,"")))="","","Missing: "&TEXTJOIN(", ",,IF(COUNTIF(D10:D52,'Employee Data'!$A$2:$A$29),"",IF('Employee Data'!$A$2:$A$29<>"",'Employee Data'!$A$2:$A$29,""))))

Ok, it looks like doing the same thing for the first half works too. Copy this formula and give it a try.

 

[steve89]

works amazingly perfect thank you - have another question as you can see the way its setup its alerts me when im missing a shift based off of whats is plugged in on "Employee Data." I want the formula to know as well if Am 1/Pm 1 is there it should not say both shifts are missing cant figure it out at all. Another question i have is if a shift is missing it alerts me is there a formula that tells you "duplicate shifts are on such as example two AM 1 shifts when only need 1. i know your helping me alot and thank you again

 

[dreid1011]

works amazingly perfect thank you - have another question as you can see the way its setup its alerts me when im missing a shift based off of whats is plugged in on "Employee Data." I want the formula to know as well if Am 1/Pm 1 is there it should not say both shifts are missing cant figure it out at all. Another question i have is if a shift is missing it alerts me is there a formula that tells you "duplicate shifts are on such as example two AM 1 shifts when only need 1. i know your helping me alot and thank you again

For the first question, I'm not entirely sure I understand what you are asking. What should the following examples display(assume all other shifts are present):

[A]
AM 1
PM 1

Result?


AM 1
missing PM 1

Result?

[C]
PM 1
missing AM 1

Result?

[D]
missing AM 1 and PM 1

Result?

Second question, can this be done in a separate formula?

 

[steve89]

thank you and sorry for the confusion - so at times an individual covers two shifts in this example Am 1/ PM 1 is what my schedule would show. The formulas currently know that if any ONE of these is shown on the schedule then nothing is missing. That being said i can't put in this example again Am 1/PM 1 in "Employee Data" tab A2:A29 because then it will always say im missing that shift. What the formula should know that if these occasions occur then those shifts are filled. for you second question yes it can be done in a second formula

 

[dreid1011]

So, if "AM 1/PM 1" shows up in your shift list instead of "AM 1" and "PM 1" individually, then it should not show those as missing, correct?

 

[steve89]

correct i will apply the rest of the shifts based off that formula

 

[dreid1011]

Do you have any limitations as to the layout of the sheets and how the problem might be solved? I'm not sure I can figure out the "AM 1/PM 1" issue and still incorporate it into the original formula. (I tried one thing and it is not working) And the other issue, about counting duplicate shifts, it might be more easily solved with a few separate formulas.

 

[steve89]

We can do separate formulas I don’t have an issue with that