Multiple IF Statements -- Status Updates

Julie_Boss85141

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.

Paul Newcome

Give something like this a try...

 

=IF(CONTAINS("no", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Denied", IF(CONTAINS("pending", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Pending"), IF(CONTAINS("yes", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Approved")))

Paul Newcome

Give something like this a try...

 

=IF(CONTAINS("no", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Denied", IF(CONTAINS("pending", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Pending"), IF(CONTAINS("yes", [Sr. Site Superintendent Approval?]@row:[Sr. Project Manager Approval?]@row), "Approved")))

Julie_Boss85141

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!!!

Paul Newcome

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

Julie_Boss85141

Paul,

 

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

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?

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.

Sue Emery

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"))