Page 1 of 5 123 ... LastLast
Results 1 to 10 of 45

Thread: Return a TEXT value if certain TEXT is missing

  1. #1
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return a TEXT value if certain TEXT is missing

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,488
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

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

  3. #3
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    what does I3 represent im not understanding this formula

  4. #4
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,488
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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

  6. #6
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  7. #7
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,488
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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.

  8. #8
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,488
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    list of all shifts IS SHIFT MISSING list of shiftsorganised for today
    this is col C
    this is col D col H
    row 5 PM COOK PM COOK
    AM COOK MISSING AM 4
    AM 4 AM 1
    AM 2 MISSING AM 3
    AM 1 PM 2
    AM 3 PM 1
    PM 3 MISSING
    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","")

  10. #10
    Board Regular
    Join Date
    Oct 2015
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    what is H4 I see H5 is " pm cook"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •