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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Re: Need help..Want to return a TEXT value if certain TEXT is missing

shift1
shift2
shift3missing
shift1shift4
shift2shift5
shift4
shift5
formula giving missing
=IF(COUNTIF($D$4:$D$7,I3)=0,"missing","")

<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

what does I3 represent im not understanding this formula
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

this is the way I lay it out

from D11:D23 there should be 1 of each shift: AM 1, AM 2, AM 3, AM 4, PM 1, PM 2 & PM 3 and the extra spaces are the employees who are off that day. I would like a formula that can tell me what shifts are missing if I'm suppose to have on any giving day the shifts listed above. If those are not here to return the value of the missing shift I tried a few things like a table too with no luck
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

it is I3 not 13 (the letter after H)

column I is the list of all your shifts
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

appreciate your help but definitely doesn't work keeps asking for value if you message me I can send you the file really need help with it thanks again
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

I have been warned not to go offline on problems by the moderators. My example is specific to the cells I chose to put my data in. So change the cell refs to match your data. If still in trouble copy and paste a few rows on here.
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

These are how the shifts are lined up on a daily basis this is what a daily schedule should read so I would like the formula to know if anyone of these are missing to return that missing shifts pretty much it needs to identify if a day is suppose to consist of all of these shifts and if any is missing return that exact shifts that I still to fill for the day so sorry for delayed response I do appreciate your help. Maybe a table would work for this what do you think I was thinking maybe a table idk.

PM COOK
AM COOK
OFF
AM 4
AM 2
AM 1
OFF
OFF
OFF
AM 3
PM 3
PM 2
PM 1
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

list of all shiftsIS SHIFT MISSINGlist of shiftsorganised for today
this is col C
this is col Dcol H
row 5PM COOKPM COOK
AM COOKMISSINGAM 4
AM 4AM 1
AM 2MISSINGAM 3
AM 1PM 2
AM 3PM 1
PM 3MISSING
PM 2
PM 1
the formula in the cell to the right of PM COOK (ie in cell D5) is
=IF(ISERROR(OFFSET($H$4,MATCH(C5,$H$5:$H$13,0),0)),"MISSING","")

<colgroup><col span="2"><col><col><col span="7"></colgroup><tbody>
</tbody>
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

what is H4 I see H5 is " pm cook"
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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