Multiple IF Statements -- Status Updates

Hello,

 

I am trying to put together a sheet that reflects as a holistic "Status Update" depending on whether or not something has been "Approved", is "Pending", or has been "Denied".

I attached a photo that shows what I am trying to get my sheet to reflect and what is happening with returned values.

 

Here are the examples of what I want my formula to do:

IF: SR. Site Superintendent Approval? = YES & Sr. Project Manager Approval? = YES then Final Status will be "APPROVED"

IF one of these columns say "NO" at all I need to Final Status column to reflect "Denied"

If both of the columns say "Pending" I need it to reflect "Pending"

If one of the statuses reads "Pending" and one says "Yes" I still need it to reflect "Pending" 

 

This is what I have been trying to get to work, but it keeps returning "0" in some of my columns, and also placing a "0" at the beginning of my "Denied" return. 

 

Help?

 

=IF(AND([Sr. Site Superintendent Approval?]16 = "PENDING", [Sr. Project Manager Approval?]16 = "PENDING"), "PENDING") + IF(AND([Sr. Site Superintendent Approval?]16 = "YES", [Sr. Project Manager Approval?]16 = "YES"), "APPROVED") + IF(OR([Sr. Site Superintendent Approval?]16 = "NO", [Sr. Project Manager Approval?]16 = "NO"), "DENIED")

 

Also Note: I was trying to add rules about "Pending If one said "Pending" and one said "Yes" = "Pending" but the formula kept returning strange.

IF Statements.PNG

Best Answer

Answers

  • That didn't work exactly right for some reason, but I tried it like this and it immediately worked:

     

    =IF(CONTAINS("NO", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "DENIED", IF(CONTAINS("PENDING", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "PENDING", IF(CONTAINS("YES", [Sr. Site Superintendent Approval?]7:[Sr. Project Manager Approval?]7), "APPROVED")))

    Thanks for the help!!! :D

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hmm... That's odd. At least you got it to work. Happy to help! yes

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Paul,

     

    Is it normal for it to glitch like that and not work with the @row?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is not, but it could have also been some other error that was not noticed at first but unintentionally fixed when removing the @row references. What error were you getting?

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Was it either #UNPARSEABLE or #INCORRECT ARGUMENT?

     

    I just noticed an extra closing parenthesis in mine just after "Pending". It may have been that you corrected that without even realizing it when removing the @row references.

    10xViz.com

    Come see me at ENGAGE 2023! I have "Happy to help 👍️" buttons!

    If you can't find me roaming the floor, I will most likely be at the 10xViz Partner Booth.

  • I have used your formula but when one column is yes and other column is no, the approval is yes instead of no. How do I get the Approval Status to change to no if one of the approval columns is no?

    Here is the formula and a screen shot of sheet:

    =IF(CONTAINS("Yes", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "Yes", IF(CONTAINS("No", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "No"))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!