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]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

also weird if i put pm 5 or anything in general regardless if it matches whats missing the formula excepts it....

 

[steve89]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

should be able to enter the shifts anywhere youd like and not be limited to that specific cell to match

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

YOU ARE A GOD OMG thank you so much it works perfectly

There you go - don't show the exact layout & requirement and you are still waiting after 18 months. Show the layout & requirement & you only have to wait less than 7 hours.

.. how in the world do you know how to do that thank you

Practice.

formula which you have for Q2 isnt working its displaying the whole formula not actually working ..

Do you have Excel through Office 365?
Displaying the whole formula usually, but not always, means that the cell is formatted as text. If so, you need to format it as General and re-enter the formula.

... also the page randomly stopped accepting the formulas

What does "stopped accepting the formulas" mean exactly?

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

also weird if i put pm 5 or anything in general regardless if it matches whats missing the formula excepts it....

should be able to enter the shifts anywhere youd like and not be limited to that specific cell to match

I have no idea what you mean by these two statements. You would need to be more specific, or show me with another screen shot and explain the problem(s) in relation to that.

 

[steve89]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
[TABLE="width: 1385"]
<colgroup><col><col><col><col span="4"><col><col span="9"><col><col></colgroup><tbody>[TR]
[TD]DAY[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Q2

#VALUE ![/TD]
[TD] R2

Missing: PM6[/TD]
[TD="align: center"]S2

#NAME?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
</body>[TABLE="width: 1385"]
<colgroup><col><col><col><col span="4"><col><col span="9"><col><col></colgroup><tbody>[TR]
[TD]DAY[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Q2

#VALUE ! [/TD]
[TD] R2

Missing: PM6[/TD]
[TD="align: center"]
S2

#NAME?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]AM COOK[/TD]
[TD]PM COOK[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

as you can see i have excel 2016 365 and the Q2 formula you provided is for 2016 and doesnt work but the R2 formula works well but the catch is lets say we reference "day 7 pm 6" if i delete "pm6" and put pm 5 it doesnt say "missing pm 6" it accepts any replacement

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

In your last post I cannot see exactly what columns and rows the data and results are in (we could see that in post #27 ) but ..

1. The #VALUE! result is most likely because you haven't entered the formula correctly as I described previously

The formula in Q2 requires Excel 2016 through Office 365 and is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

2. The #NAME? error is most likely because you did not enter the code correctly as I described previously ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

.. or you have not used the exact same spelling for the function name in the worksheet as in the code.


In relation to the other issues you have raised, I am confused about what you actually are doing in your worksheet. In post #27 you showed shift headings in row 1 with people's names below them and you apparently wanted to know which shifts didn't have a name under them.
In your latest post you have shift names in all rows and apparently want to know which shift names have anything except themselves underneath. This is a very different requirement. Naturally, if you change what you have and what you are trying to do, previous suggestions are unlikely to work.

Please show us what you actually have, where and put in the result(s) you want, not ones that are not working.

 

[steve89]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

okay so i was just needing to do the "control, shift and enter" to make it work so that solves that (thank you). But the issue is these are the shifts i need per day in NO specific order but the formula should know if I'm missing any shift. In this case the formula is working well but if i erase lets say "pm6" and just put "pm5" it accepts it even though I'm still technically missing "pm6"

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

.. i need per day in NO specific order but the formula should know if I'm missing any shift. .. if i erase lets say "pm6" and just put "pm5" it accepts it even though I'm still technically missing "pm6"

Try

Excel Workbook
	B	C	D	E	F	G	H	I	J	Q
1	AM COOK	PM COOK	AM1	AM2	AM3	PM4	PM5	PM6		Missing
2	AM COOK	PM COOK	AM1	AM2	AM3	PM4	PM5			PM6
3	AM COOK	PM COOK	AM1	AM2	AM3	PM4	PM6			PM5
4	AM3	AM3	AM3	AM3	AM3					AM COOK, PM COOK, AM1, AM2, PM4, PM5, PM6
5										AM COOK, PM COOK, AM1, AM2, AM3, PM4, PM5, PM6
6	PM6	PM5	PM4	AM3	AM2	AM1	PM COOK	AM COOK
7		AM COOK		pm4				PM6		PM COOK, AM1, AM2, AM3, PM5
Missing Shifts (2)

 

[steve89]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

that works exactly how its suppose to not just limiting the shift at a specific cell but heres my last question can you still incorporate the "missing" part of it??

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

that works exactly how its suppose to not just limiting the shift at a specific cell but heres my last question can you still incorporate the "missing" part of it??

{=IF(TEXTJOIN(", ",,IF(COUNTIF(B2:I2,B$1:I$1),"",B$1:I$1))="","","Missing: "&TEXTJOIN(", ",,IF(COUNTIF(B2:I2,B$1:I$1),"",B$1:I$1)))}