IF statement works independently but not when combined together in one statement

jnevills66866
jnevills66866 ✭✭✭
edited 12/09/19 in Smartsheet Basics

I have been trying to combine several IF statements into one statement but with out any luck. The If statements work independently but not once I combine them together. The goal is when a date is enter to one of the 3 columns, it will populate from a drop down in another column. 

If the "Sent Back-Out of Area" column has a date entered, it will add "Out of Area" to the "Sent Back" column . =IF([Sent Back-Out of Area]2 <> "", "Out of Area", "")

If the "Sent Back-4th Attempt" column has a date entered, it will add "4th Attempt" to the "Sent Back" column. =IF([Sent Back-4th attempt]19 <> "", "4th Attempt", "")

If the "Sent Back-Issue" column has a date entered, it will add "Issue" to the "Sent Back" column. =IF([Sent Back-Issue]18 <> "", "Issue", "")

Here is what I have once I put everything together: 

=IF([Sent Back-Out of Area]@row <> "", "Out of Area", "",IF([Sent Back-4th attempt]@row <> "", "4th Attempt", "",([Sent Back-Issue]@row <> "", "Issue", "") but it is says UNPARSEABLE.

I have also tried this: =IF(ISDATE([Sent Back-Out of Area]1)), "Out of Area", "", IF(ISDATE([Sent Back-4th attempt]1)), "4th Attempt", "", IF(ISDATE([Sent Back-Issue]1)), "Issue", "") but again it is says UNPARSEABLE. 

Any ideas what I am doing wrong?

 

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    If(Criteria,true,false)

    Above is the format for an if statement. The format you are using is

    if(criteria,true,false,if

    You can't just take an if on the end of another statement. You need to put it in the true or false returns.

    Example:

    =IF([Sent Back-Out of Area]@row <> "", "Out of Area",IF([Sent Back-4th attempt]@row <> "", "4th Attempt",""))

    Criteria = sent back out of area is not black

    true = Out of area

    false = next if statement

    You create your priority of returns by the order of the if statements. It is kind of like an upside down tree.

     

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You've got some excess parentheses in there. As well as some excess ELSE ""'s. Try one of these: 

    =IF([Sent Back-Out of Area]@row <> "", "Out of Area",IF([Sent Back-4th attempt]@row <> "", "4th Attempt", IF([Sent Back-Issue]@row <> "", "Issue", "")))

    =IF(ISDATE([Sent Back-Out of Area]1), "Out of Area", IF(ISDATE([Sent Back-4th attempt]1), "4th Attempt", IF(ISDATE([Sent Back-Issue]1), "Issue", "")))

    Nested If statements follow this standard forumla,

    =IF(This criterion, "Then this", IF(This different criterion, "Then That", IF(This third criterion, "This this third option", "Final Option")))

  • L_123
    L_123 ✭✭✭✭✭✭

    Man I really wish they would fix this cloudflare thing. I never spot my typos until after I post.

  • Thank you for all your help! I lost track of how many different ways I wrote this formula. I started to confuse myself. This is what ended up working.

    =IF([Sent Back-Out of Area]@row <> "", "Out of Area", IF([Sent Back-4th attempt]@row <> "", "4th Attempt", IF([Sent Back-Issue]@row <> "", "Issue", "")))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. I am glad I could help you out. Nested IF statements can be a bear to create.

  • Hi @Mike Wilday or anyone else who may be able to help.

    I am having the same issue with getting the #UNPARSABLE error when trying to combine two IF statements.

    =IF(Status@row = "Check Box"), INDEX({Destination Sheet Range 1}, MATCH([Row ID]@row, {Destination Sheet Range 3})), IF(Status@row <> "", IF(AND(Status@row <> "Complete", Status@row <> "Not Required", Status@row <> "Check Box"), TODAY()),"")

    Are you able to spot the parsing error in the above (or able to simplify the above in any way)?

    Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/04/20

    @Kumiko Percival The parsing error itself is coming from the closing parenthesis after your first "Check Box".

    You can also simplify just a little by removing the <> "Check Box" criteria from the AND statement. Since the first IF states that Status@row = "Check Box", to make it to the next IF then the status MUST NOT be "Check Box". You can also include the Status@row <> "" criteria within the AND statement to save yourself from a set of parenthesis.


    =IF(Status@row = "Check Box", INDEX({Destination Sheet Range 1}, MATCH([Row ID]@row, {Destination Sheet Range 3})), IF(AND(Status@row <> "", Status@row <> "Complete", Status@row <> "Not Required"), TODAY()))

  • Thanks Paul, your formula worked perfectly!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    @Paul Newcome Thanks for chiming in!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Mike Wilday Sure thing. I wasn't sure when you would be on next, so I figured I'd go ahead and try to help out.