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 an update 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 a Nested 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 if any of the cells say this then we should display this text.

IF(OR([Approver 1 Approval]@row = "Changes Needed", [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 a Nested 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 if any of the cells say this then we should display this text.

IF(OR([Approver 1 Approval]@row = "Changes Needed", [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 Formula for 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! 🙂