Require all approvals received before setting status to Approved

ktcran

I have this successfully working column formula in Overall Approval Status:

=IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Declined") > 0, "Declined", IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Approved") > 0, "Approved", IF([TEAM A - Approval Requested]@row = 1, "Submitted", "")))

From AI: The formula checks for 'Declined' in the approval columns from Kashif to Adam. If found, it returns 'Declined'. If not, it checks for 'Approved' and returns 'Approved'. If neither, it checks if 'TEAM A - Approval Requested' is checked and returns 'Submitted'. Otherwise, it returns an empty string.

I need to update this formula to return "Submitted" in the Overall Approval Status column until all cells with 'Submitted' have been changed.

Paul McGuinness

Hi @ktcran

Reordering your formula should do the trick,

=IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Declined") > 0,"Declined",IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Submitted") > 0, "Submitted", "Approved"))

It now checks for any instance of declined and returns declined, it then looks for any instance of submitted and returns submitted, if there are no instances of declined or submitted then the assumption is that everything is approved.

Hope that helps

Paul McGuinness
Central Operations Manager at Care UK

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

Paul McGuinness

Hi @ktcran

Reordering your formula should do the trick,

=IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Declined") > 0,"Declined",IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Submitted") > 0, "Submitted", "Approved"))

It now checks for any instance of declined and returns declined, it then looks for any instance of submitted and returns submitted, if there are no instances of declined or submitted then the assumption is that everything is approved.

Hope that helps

Paul McGuinness
Central Operations Manager at Care UK

If you found this comment useful, please let me know by clicking one of the buttons below: Awesome, Insightful, Upvote, or Accepted Answer. Your feedback will assist others looking for the same information and also help me out.

ktcran

Thank you @Paul McGuinness - this turned out to be half correct. I still needed to include the part of the formula that looks at the checkbox but it all checks out now!

=IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Declined") > 0, "Declined", IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Submitted") > 0, "Submitted", IF(COUNTIF([Approval - Kashif]@row:[Approval - Adam]@row, "Approved") = COUNTM([Approval Contact (Product Manager, Kashif, Quincy)]@row), "Approved", IF([TEAM A - Approval Requested]@row = 1, "Submitted", ""))))