How do i create a status column based off of levels and approvals?

I have to create a status column that uses 3 different levels that each require different people to approve. how do i make one big formula for it all. For example level three needs person one to approve, then person two, then person three. then the status can be complete. basically i need if status is level 2 then person one needs to equal approve, person two equal approve, person three equal approve.

can I even make this a percent of conditioning format so that the status is yellow color for not completely approved by everyone yet? and green when complete?

Find more posts tagged with

Formulas

Accepted answers

Danielle Arteaga

It sounds like you need two things. First, you need Request Approval workflows. This will send an email to each approver based on the conditions you set. So, for example, you can create a workflow that sends an email to the approver who would need to approve a Level Three task. Here's a good article on how to create a workflow that requests approvals from stakeholders: https://help.smartsheet.com/articles/2479276-request-approval-from-stakeholder-%20in-sequence

Second, you need a formula in a "Symbol" type column. It's hard to help here without seeing the structure of your worksheet, but it would be something like this:

=IF(AND([Person One Action]@row="Approve", [Person Two Action]@row="Approve", [Person Three Action]@row="Approve"), "Green", "Yellow")

What this tells Smartsheet to do is place a Green circle in the column only if ALL of the approvers have approved. If that condition isn't met, it will place a Yellow circle in the column. You can also write this so that it is Green if all approvers have approved, Yellow if only 1 or 2 approvers have approved, and Red if no approvers have approved.

That would look something like this:

=IF(AND([Person One Action]@row = "Approve", [Person Two Action]@row = "Approve", [Person Three Action]@row = "Approve"), "Green", IF(OR([Person One Action]@row = "Approve", [Person Two Action]@row = "Approve", [Person Three Action]@row = "Approve"), "Yellow", "Red"))

Screenshot 2022-11-04 at 10.50.58 AM.png

All comments

Danielle Arteaga

Second, you need a formula in a "Symbol" type column. It's hard to help here without seeing the structure of your worksheet, but it would be something like this:

=IF(AND([Person One Action]@row="Approve", [Person Two Action]@row="Approve", [Person Three Action]@row="Approve"), "Green", "Yellow")

That would look something like this:

Please help

I thought that it would work and it is set up the same way that my sheet is but I do not know why it is not working?

Any ideas?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.