Formula to show Current Status of Project Approval

SChristJLL

Not sure if this would be accomplished easier with a Formula or with a Update Cell Workflow.

We have a Projects Smartsheet that have 4 different Reviewers that need to approve. I'm looking to add another column for Reporting purposes that will show what approval step each project is on.

For example, based off my attached screenshot I'd like to have a formula in a new "Current Status" column return that PTP Name UPS Annual is "Pending Dell FM Review".

I'm a beginner with Smartsheet formulas, but was thinking there is an IF statement I could write to pull this off.

Any suggestions would be greatly appreciated!!!

Julio S.

Hi @SChristJLL,

You may want to try inserting in your new "Current Status" column:

=IF(COUNTIF([JLL FM Review status]@row:[Dell EHS Review Status]@row, "Approved") = 4, "Approved", IF([JLL FM Review status]@row = "", "Pending JLL Review status", IF([JLL OPs Review Status]@row = "", "Pending JLL OPs Review status", IF([Dell FM Review Status]@row = "", "Pending Dell FM Review Status", IF([Dell EHS Review Status]@row = "", "Pending Dell EHS Review Status")))))

You can then make this into a Column Formula (see this article to learn how). Note that if you'd like to replace empty cells with some text in the line of "Pending", all " " in the formula will need to be replaced accordingly.

You can also find COUNTIF and IF are helpful reference to better understand the functions I used in that specific formula and I would also recommend this article that contains helpful tips to make your formulas more efficient and better prepared to be used in a Column formula. 

If you'd like to see more introductory information about formulas, you may find the Formula Webinar Series and the Create and Edit Formulas article quite useful as well.

I hope this can help.

Cheers,

Julio

Julio S.

Hi again @SChristJLL,

Sure, I'm happy to help! In this case you'd like to implement additional OR functions for each IF statement so that "" or "On Hold" are considered in the same way to trigger the response from the respective IF statement.

Since each condition will now need to recognize two different values, this will make the formula a little bit longer but should look like the following:

=IF(COUNTIF([JLL FM Review status]@row:[Dell EHS Review Status]@row, "Approved") = 4, "Approved", IF(OR([JLL FM Review status]@row = "", [JLL FM Review status]@row = "On Hold"), "Pending JLL Review status", IF(OR([JLL OPs Review Status]@row = "", [JLL OPs Review Status]@row = "On Hold"), "Pending JLL OPs Review status", IF(OR([Dell FM Review Status]@row = "", [Dell FM Review Status]@row = "On Hold"), "Pending Dell FM Review Status", IF(OR([Dell EHS Review Status]@row = "", [Dell EHS Review Status]@row = "On Hold"), "Pending Dell EHS Review Status")))))

I hope this can help. Let me know if you face any road-block and I'll be happy to advise.

Cheers,

Julio

Julio S.

Hi @SChristJLL,

You may want to try inserting in your new "Current Status" column:

=IF(COUNTIF([JLL FM Review status]@row:[Dell EHS Review Status]@row, "Approved") = 4, "Approved", IF([JLL FM Review status]@row = "", "Pending JLL Review status", IF([JLL OPs Review Status]@row = "", "Pending JLL OPs Review status", IF([Dell FM Review Status]@row = "", "Pending Dell FM Review Status", IF([Dell EHS Review Status]@row = "", "Pending Dell EHS Review Status")))))

You can then make this into a Column Formula (see this article to learn how). Note that if you'd like to replace empty cells with some text in the line of "Pending", all " " in the formula will need to be replaced accordingly.

You can also find COUNTIF and IF are helpful reference to better understand the functions I used in that specific formula and I would also recommend this article that contains helpful tips to make your formulas more efficient and better prepared to be used in a Column formula. 

If you'd like to see more introductory information about formulas, you may find the Formula Webinar Series and the Create and Edit Formulas article quite useful as well.

I hope this can help.

Cheers,

Julio

SChristJLL

https://community.smartsheet.com/discussion/comment/312207#Comment_312207

Awesome, thanks Julio!

I'm definitely going to insert the new column, probably right next to the Project Name.

I'm going to play around with your formula, thank you for pointing me in the right direction. I was not utilizing the COUNTIF originally.

Also, thank you for the links to more information! I'm just getting started with more in depth formulas, and this will be a big help!

I'll let you know if this ends up working for me, I appreciate the response!

Cheers

SChristJLL

Julio, you're a lifesaver! Formula worked like a charm.

Regarding your comment of : "Note that if you'd like to replace empty cells with some text in the line of "Pending", all " " in the formula will need to be replaced accordingly."

Instead of replacing, can the formula account for both? Those status' can either be blank "" or "On Hold". Am I able to edit the formula to account for if those are blank or On Hold?

Julio S.

Hi again @SChristJLL,

Sure, I'm happy to help! In this case you'd like to implement additional OR functions for each IF statement so that "" or "On Hold" are considered in the same way to trigger the response from the respective IF statement.

Since each condition will now need to recognize two different values, this will make the formula a little bit longer but should look like the following:

=IF(COUNTIF([JLL FM Review status]@row:[Dell EHS Review Status]@row, "Approved") = 4, "Approved", IF(OR([JLL FM Review status]@row = "", [JLL FM Review status]@row = "On Hold"), "Pending JLL Review status", IF(OR([JLL OPs Review Status]@row = "", [JLL OPs Review Status]@row = "On Hold"), "Pending JLL OPs Review status", IF(OR([Dell FM Review Status]@row = "", [Dell FM Review Status]@row = "On Hold"), "Pending Dell FM Review Status", IF(OR([Dell EHS Review Status]@row = "", [Dell EHS Review Status]@row = "On Hold"), "Pending Dell EHS Review Status")))))

I hope this can help. Let me know if you face any road-block and I'll be happy to advise.

Cheers,

Julio

SChristJLL

Thank you so much @Julio S.!

This helped tremendously and did the trick. I appreciate your quick responses.

Kudos, take care!