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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!