Getting the wrong output from IF status formula

DWNeuro

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

DWNeuro

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

Nic Larsen

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.

DWNeuro

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