Getting the wrong output from IF status formula
Hello Smartsheet World,
I am trying to fix a formula to give me the most recent status given in a collection of columns. for some reason the last column is showing as Approved even though it is labeled Pending review. the rest of the formula works as needed.
The formula is as follows:
=IF([Level 4 Approval]@row <> "", "Approved ", IF([Level 4 Approval]@row = "Pending Review", "Awaiting Approval from Finance", IF([Level 4 Approval]@row = "Declined", "Declined by Finance", IF([Level 3 Approval]@row = "Pending Review", "Awaiting Approval from Program Lead", IF([Level 3 Approval]@row = "Declined", "Declined by Program Lead", IF([Level 2 Approval]@row = "Pending Review", "Awaiting Approval from Clin Dev Lead", IF([Level 2 Approval]@row = "Declined", "Declined by Clin Dev Lead", IF([Level 1 Approval]@row = "Pending Review", "Awaiting Approval from Clin Ops Director", IF([Level 1 Approval]@row = "Declined", "Declined by Clin Ops Director", IF([Outsourcing Confirmation]@row = "Pending Review", "Awaiting Outsourcing Confirmation", IF([Outsourcing Confirmation]@row = "Declined", "Declined by Outsourcing", IF([Outsourcing Confirmation]@row = "", "Awaiting Outsourcing Confirmation"))))))))))))
Best Answer
-
I took the advice and played around. I needed to place that approved statement at the end of the "level 4" IF statements. Thank you for the tip! working formula as follows
=IF([Level 4 Approval]@row = "Pending Review", "Awaiting Approval from Finance", IF([Level 4 Approval]@row = "Declined", "Declined by Finance", IF([Level 4 Approval]@row <> "", "Approved", IF([Level 3 Approval]@row = "Pending Review", "Awaiting Approval from Program Lead", IF([Level 3 Approval]@row = "Declined", "Declined by Program Lead", IF([Level 2 Approval]@row = "Pending Review", "Awaiting Approval from Clin Dev Lead", IF([Level 2 Approval]@row = "Declined", "Declined by Clin Dev Lead", IF([Level 1 Approval]@row = "Pending Review", "Awaiting Approval from Clin Ops Director", IF([Level 1 Approval]@row = "Declined", "Declined by Clin Ops Director", IF([Outsourcing Confirmation]@row = "Pending Review", "Awaiting Outsourcing Confirmation", IF([Outsourcing Confirmation]@row = "Declined", "Declined by Outsourcing", IF([Outsourcing Confirmation]@row = "", "Awaiting Outsourcing Confirmation"))))))))))))
Answers
-
It looks to me that the first argument in your IF statement is showing as True, so it is resulting in the Approved status.
IF([Level 4 Approval]@row <> "", "Approved ",
Level 4 on that row is not blank….
Maybe move that section to the end of your formula if it is still needed, so the rest of the IF's go first and that is your last check.
-
I took the advice and played around. I needed to place that approved statement at the end of the "level 4" IF statements. Thank you for the tip! working formula as follows
=IF([Level 4 Approval]@row = "Pending Review", "Awaiting Approval from Finance", IF([Level 4 Approval]@row = "Declined", "Declined by Finance", IF([Level 4 Approval]@row <> "", "Approved", IF([Level 3 Approval]@row = "Pending Review", "Awaiting Approval from Program Lead", IF([Level 3 Approval]@row = "Declined", "Declined by Program Lead", IF([Level 2 Approval]@row = "Pending Review", "Awaiting Approval from Clin Dev Lead", IF([Level 2 Approval]@row = "Declined", "Declined by Clin Dev Lead", IF([Level 1 Approval]@row = "Pending Review", "Awaiting Approval from Clin Ops Director", IF([Level 1 Approval]@row = "Declined", "Declined by Clin Ops Director", IF([Outsourcing Confirmation]@row = "Pending Review", "Awaiting Outsourcing Confirmation", IF([Outsourcing Confirmation]@row = "Declined", "Declined by Outsourcing", IF([Outsourcing Confirmation]@row = "", "Awaiting Outsourcing Confirmation"))))))))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!