Getting an image approved - how to set Approval Status from multiple cells, ignoring blanks

Heather Walker

Hello!

I have an image production process sheet that at the moment requires 1-3 approvers per image. The images to be approved live in a cloud drive - they do not get attached to the Smartsheet.

Basically, when there is a contact email in an Approver cell, I need a formula to check what the Image Approval Status is.

Here is the flow -

1. Vendor submits link, hits checkbox "Send for Review", automation sends anupdate request*to contacts in Approver 1, Approver 2, and Approver 3 cells. Approver 2 and Approver 3 might be blank if they are not needed. *This is not set in stone, was using this method so that they can also submit comments on what needs to be changed.
2. Approver 1, Approver 2, and Approver 3 submit Update Request with "Approved" or "Changes Needed" along with comments in Approver 1 Comments, Approver 2 Comments, Approver 3 Comments.

Here's the logic:

Here's what the cells look like currently:

Different section of the sheet contains the Approver 1 - 3 contact columns:

Thanks for any help!

Heather

Genevieve P.

Hi@Heather Walker

Thanks for describing your process so well, and for the screen captures! This is really helpful.

This can be done with aNested IF formula statement.

The first thing we'll say is that if the first Approver cell is blank, return a blank status (as I presume this means the process hasn't started yet).

=IF([Approver 1]@row = "", ""

Then we'll move on to the "Changes Needed" instruction, because ifanyof the cells say this then we should display this text.

如果(或([1审批人批准]@row =“改变需要“, [Approver 2 Approval]@row = "Changes Needed", [Approver 3 Approval]@row = "Changes Needed"), "Changes Needed"

Then we'll move on to if Three Approvers have been assigned, but not all three have responded. This counts how many cells have content in the 3 Approver cells, then compares it to how many responses have been received. This presumes that you add in all possible approvers at the beginning before sending out the requests.

IF(COUNT([Approver 1]@row, [Approver 2]@row, [Approver 3]@row) <> COUNT([Approver 1 Approval]@row, [Approver 2 Approval]@row, [Approver 3 Approval]@row), "Waiting on Response"

That covers it! Now we can say if neither if the above statements are true (so all approvals have a response, but none of them say "Changes Needed"), it's Approved!

, "Approved"

Full Formula:

=IF([Approver 1]@row = "", "", IF(OR([Approver 1 Approval]@row = "Changes Needed", [Approver 2 Approval]@row = "Changes Needed", [Approver 3 Approval]@row = "Changes Needed"), "Changes Needed", IF(COUNT([Approver 1]@row, [Approver 2]@row, [Approver 3]@row) <> COUNT([Approver 1 Approval]@row, [Approver 2 Approval]@row, [Approver 3 Approval]@row), "Waiting on Response", "Approved")))

Now you can make it a Column Formula!

Let me know if this works for you.

Cheers,

Genevieve

Genevieve P.

Hi@Heather Walker

Thanks for describing your process so well, and for the screen captures! This is really helpful.

This can be done with aNested IF formula statement.

The first thing we'll say is that if the first Approver cell is blank, return a blank status (as I presume this means the process hasn't started yet).

=IF([Approver 1]@row = "", ""

Then we'll move on to the "Changes Needed" instruction, because ifanyof the cells say this then we should display this text.

如果(或([1审批人批准]@row =“改变需要“, [Approver 2 Approval]@row = "Changes Needed", [Approver 3 Approval]@row = "Changes Needed"), "Changes Needed"

Then we'll move on to if Three Approvers have been assigned, but not all three have responded. This counts how many cells have content in the 3 Approver cells, then compares it to how many responses have been received. This presumes that you add in all possible approvers at the beginning before sending out the requests.

IF(COUNT([Approver 1]@row, [Approver 2]@row, [Approver 3]@row) <> COUNT([Approver 1 Approval]@row, [Approver 2 Approval]@row, [Approver 3 Approval]@row), "Waiting on Response"

That covers it! Now we can say if neither if the above statements are true (so all approvals have a response, but none of them say "Changes Needed"), it's Approved!

, "Approved"

Full Formula:

=IF([Approver 1]@row = "", "", IF(OR([Approver 1 Approval]@row = "Changes Needed", [Approver 2 Approval]@row = "Changes Needed", [Approver 3 Approval]@row = "Changes Needed"), "Changes Needed", IF(COUNT([Approver 1]@row, [Approver 2]@row, [Approver 3]@row) <> COUNT([Approver 1 Approval]@row, [Approver 2 Approval]@row, [Approver 3 Approval]@row), "Waiting on Response", "Approved")))

Now you can make it a Column Formula!

Let me know if this works for you.

Cheers,

Genevieve

Heather Walker

Hi@Genevieve P- this is perfect!

With this formula + my automation on "Send to Review", this is really going to smooth out the process!

I added one nested IF statement to the formula -

• IF "Send for Review" is NOT checked (image is still being worked on)
• AND there are Approvers contact information entered
• THEN Approval Status is set to "Not Sent for Review."

Full Formulafor checkbox as the trigger to send for review:

=IF([Send for Review]@row = 1, IF([Approver 1]@row = "", "", IF(OR([Approver 1 Approval]@row = "Changes Needed", [Approver 2 Approval]@row = "Changes Needed", [Approver 3 Approval]@row = "Changes Needed"), "Changes Needed", IF(COUNT([Approver 1]@row, [Approver 2]@row, [Approver 3]@row) <> COUNT([Approver 1 Approval]@row, [Approver 2 Approval]@row, [Approver 3 Approval]@row), "Waiting on Response", "Approved"))), "Not Sent for Review")

Thanks so much for your help!

Heather

Genevieve P.

Hi@Heather Walker

Amazing! That's a great addition. Looks good!