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

 

[steve89]

hi i need help with this formula. It displays if a shift is missing but when a cell is empty from A2:A29 its says "missing o" is there an iferror formula to put in there so i dont have to put " ' " so that the message of "missing o" isnt visible?


=IF(TEXTJOIN(", ",,IF(COUNTIF(F10:F52,'Employee Data'!$A$2:$A$29),"",'Employee Data'!$A$2:$A$29))="","","Missing: "&TEXTJOIN(", ",,IF(COUNTIF(F10:F52,'Employee Data'!$A$2:$A$29),"",'Employee Data'!$A$2:$A$29)))

 

[steve89]

here is the example

Excel Workbook
	D	E
20	OFF	AM 1
21	AM 1	AM 2
22	VAC	VAC
23	AM 4	AM 3
24	AM 3	OFF
25	N/A	AM 4
26	PM 1	PM 1
27	AM 2	OFF
28	PM 3	PM 2
29	OFF	PM 3
30	OFF	PM 4
31	PM 4	AM 1
32	PM 2	OFF
53
54		Missing: PM COOK
55
MASTER UNIVERSAL SCHEDULE

 

[dreid1011]

It would help me to see what is in Employee Data!A2:A29 as well.

 

[steve89]

Hi thanks for replying here is the data

Excel Workbook
	A
2	AM COOK
3	PM COOK
4	PR COOK
5	AM 1
6	AM 2
7	AM 3
8	AM 4
9	PM 1
10	PM 2
11	PM 3
12	PM 4
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Employee Data

 

[dreid1011]

This seems to help:

{=IF(TEXTJOIN(", ",,IF(COUNTIF(E10:E52,'Employee Data'!$A$2:$A$29),"",'Employee Data'!$A$2:$A$29))="","","Missing: "&TEXTJOIN(", ",,IF(COUNTIF(E10:E52,'Employee Data'!$A$2:$A$29),"",IF('Employee Data'!$A$2:$A$29<>"",'Employee Data'!$A$2:$A$29,""))))}

Replace the last instance of 'Employee Data'!$A$2:$A$29 with the highlighted portion.

 

[steve89]

LIKE THIS

Excel Workbook
	D
54	#VALUE!
55
MASTER UNIVERSAL SCHEDULE

 

[dreid1011]

You still have to enter it as an array. See if that helps? It looks right.

 

[steve89]

works great but now it shows "missing" lol should be blank sort of like an error if 1/1 type formula so it shows nothing when nothing is missing or empty

 

[dreid1011]

Ah, sorry. I was just addressing the second part of the formula and I was only working with half your data in columns D/E. Let me see about the first part.

 

[steve89]

thank you