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.
Best Answer
-
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")))
Answers
-
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")))
-
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!!!
-
Hmm... That's odd. At least you got it to work. Happy to help!
-
-
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?
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!