Update a status column based on multiple conditions

Christina Riot

I have a sheet tracking a project wherein the status will be changed multiple times depending on the phase of the project. Right now I'm trying to set up an autofill of the status to say "Director Approval" when the approval workflow is received by the director, then I want the status to update when the next approval is received by the Executive Director. The formula i have works, except that it will only recognize the first condition. It will not change the status when a new condition is added.

This is what I have:

=IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED", IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED", IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL")))

I played around adding an OR at the front of each, but that didn't work.

Thank you!

Andrew Stewart

Hi Christina,

Try reversing the order of the tests from last to first, ie check for the last status first. The problem is that once the CCA FORM RECEIVED is set to 1, your IF statement returns true so it never goes on to the next test.

Regards,

Andrew

Andrée Starå

Hi Christina,

I agree with Andrew.

Try this.

=IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL",IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED",IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED")))

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Please help the Community by marking the post that helped answer your question or solve your problem with the accepted answer. It will make it easier for others to find a solution or help to answer!

Christina Riot

Thank you both! This seems to work so far.

I really appreciate it!

Andrew Stewart

Hi Christina,

Try reversing the order of the tests from last to first, ie check for the last status first. The problem is that once the CCA FORM RECEIVED is set to 1, your IF statement returns true so it never goes on to the next test.

Regards,

Andrew

Andrée Starå

Hi Christina,

I agree with Andrew.

Try this.

=IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL",IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED",IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED")))

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Please help the Community by marking the post that helped answer your question or solve your problem with the accepted answer. It will make it easier for others to find a solution or help to answer!

Christina Riot

Thank you both! This seems to work so far.

I really appreciate it!

Andrée Starå

Excellent!

Happy to help!