Return a TEXT value if certain TEXT is missing

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
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
 
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....
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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;">
DAYAM COOKPM COOKAM1AM2AM3PM4PM5PM6
1AM COOKPM COOKAM1AM2AM3PM4PM5Q2

#VALUE !
R2

Missing: PM6
S2

#NAME?
2AM COOKPM COOKAM1AM2AM3PM4PM5PM6
3AM COOKPM COOKAM1AM2AM3PM4PM5PM6
4AM COOKPM COOKAM1AM2AM3PM4PM5PM6
5AM COOKPM COOKAM1AM2AM3PM4PM5PM6
6AM COOKPM COOKAM1AM2AM3PM4PM5PM6
7AM COOKPM COOKAM1AM2AM3PM4PM5PM6

<colgroup><col><col><col><col span="4"><col><col span="9"><col><col></colgroup><tbody>
</tbody>
</body>
DAYAM COOKPM COOKAM1AM2AM3PM4PM5PM6
1AM COOKPM COOKAM1AM2AM3PM4PM5Q2

#VALUE !
R2

Missing: PM6

S2

#NAME?
2AM COOKPM COOKAM1AM2AM3PM4PM5PM6
3AM COOKPM COOKAM1AM2AM3PM4PM5PM6
4AM COOKPM COOKAM1AM2AM3PM4PM5PM6
5AM COOKPM COOKAM1AM2AM3PM4PM5PM6
6AM COOKPM COOKAM1AM2AM3PM4PM5PM6
7AM COOKPM COOKAM1AM2AM3PM4PM5PM6

<colgroup><col><col><col><col span="4"><col><col span="9"><col><col></colgroup><tbody>
</tbody>

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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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"
 
Upvote 0
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
BCDEFGHIJQ
1AM COOKPM COOKAM1AM2AM3PM4PM5PM6Missing
2AM COOKPM COOKAM1AM2AM3PM4PM5PM6
3AM COOKPM COOKAM1AM2AM3PM4PM6PM5
4AM3AM3AM3AM3AM3AM COOK, PM COOK, AM1, AM2, PM4, PM5, PM6
5AM COOK, PM COOK, AM1, AM2, AM3, PM4, PM5, PM6
6PM6PM5PM4AM3AM2AM1PM COOKAM COOK
7AM COOKpm4PM6PM COOK, AM1, AM2, AM3, PM5
Missing Shifts (2)
 
Last edited:
Upvote 0
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??
 
Upvote 0
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)))}
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top