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