Getting an image approved  how to set Approval Status from multiple cells, ignoring blanks
Hello!
I have an image production process sheet that at the moment requires 13 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 
 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.
 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
Best Answer

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
Answers

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

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

Amazing! That's a great addition. Looks good! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!