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

t.siddall101151

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".

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?