Return a TEXT value if certain TEXT is missing

[steve89]

I really need help with this formula. This is what I'm looking for

I'm writing a schedule but want to make it very automated for the managers. I have the following positions on my schedule: AM 1, AM 2, AM 3, AM 4, PM 1, PM 2 & PM 3. These positions are on everyday. In that specific space sequence. I want the sheet to know if column d9:d23 were missing any of those shifts to automatically return that exact missing shift. I know how to use in conditional formula but don't want color coding just want it to return the missing shift if these are suppose to be all the shifts for 1 day.

Thank you kindly all love this site great help and knowledgeable smart individuals

 

[steve89]

this works amazingly well thank you very much. I do have a last question conversation #57 if those are the shifts we need the schedule knows whats missing and duplicate now but what if we combine a shift and add to that same tab which again is on conversation 57 if someone picked up am1/pm1 can the formula know that both arent missing either? Thanks

 

[steve89]

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	AM/PM1
Employee Data

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

 

[Peter_SSs]

I'm afraid that it has become too complicated for me.