Change Status to "Submitted for Approval" or ""Returned to Action Party"

1. I have Action Items that can be reviewed by up to 3 Reviewers (Reviewer 1, Reviewer 2, Reviewer 3) before being Submitted for Approval. Reviewers are identified by Name. Reviews are done in parallel.

2. Some Action Items are reviewed by all 3 Reviewers, others by just 1 or 2 Reviewers.

3. After review the Reviewer uses a drop down in an adjacent cell to enter "Approved by Reviewer X" if the item is approved, or "Returned by Reviewer X" if the item it not approved.

4. I want the Status column to automatically change to "Submitted for Approval" given the following conditions:

- if there is an "Approved by Reviewer X" in ALL the adjacent cells that have a Named Reviewer identified. That could be 1, 2 or all 3 Reviewers.

- if any of the Named Reviewer cells are blank i.e. there are only 1 or 2 Reviewers then there will be no "Approved by Reviewer X" in the adjacent cell. These blank cells should be ignored.

5. I want the Status column to change to "Returned to Action Party" if ANY cell says "Returned by Reviewer X".

2019-09-10 Reviews Completed - Submit for Approval.PNG

Find more posts tagged with

Formulas

Project Management

Comments

Paul Newcome

I have a few ideas, but I will have to get back to you after some testing.

Paul Newcome

I misread the post initially and was making it WAY more complicated than it needed to be.

Here's a breakdown of what should work for you.

We are looking at a range of 6 cells. [Reviewer 1]@row:[Reviewer 3 Response]@row.

If ANY of those cells contain the word "Returned", we want to generate "Returned to Action Party".

=IF(CONTAINS("Returned", [Reviewer 1]@row:[Reviewer 3 Response]@row), "Returned to Action Party",

Now for the "tricky" part. We need to first establish how many responses we need. To do this we will count how many cells within the range contain text and then divide that by 2.

=COUNTIFS([Reviewer 1]@row:[Reviewer 3 Response]@row, ISTEXT(@cell)) / 2

If there are 4 cells within our range containing text then we have 2 responses, if 6 are text then 3 responses, and if only 2 are text, then 1 of them is a response.

Now we count how many cells contain the word "Approved".

=COUNTIFS([Reviewer 1]@row:[Reviewer 3 Response]@row, CONTAINS("Approved", @cell))

If that second number matches the total number of responses we require, then all responses are approved in which case we will be generating "Submitted for Approval".

=IF(CONTAINS("Returned", [Reviewer 1]@row:[Reviewer 3 Response]@row), "Returned to Action Party", IF(COUNTIFS([Reviewer 1]@row:[Reviewer 3 Response]@row, ISTEXT(@cell)) / 2 = COUNTIFS([Reviewer 1]@row:[Reviewer 3 Response]@row, CONTAINS("Approved", @cell)), "Submitted for Approval"))

Does this work for you?

Sign in to join the conversation!

Quick Links

Trending discussions

How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.
Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])