Changing a cell value based on multiple criteria

Christopher Pretty

Hi everyone. I have been attempting this formula all day with no luck. Hopefully someone here can help out.

I have a sheet with the following columns: Approved, Approval Need, Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, Reg Sr. Dir Comm Sol Sales Ops Approval, VP Approval

The Approved Column (symbol) has the following options: Yes, Hold, No

The Approval Needed column has the following options: No, Send for Concurrent Approvals, VP Approval Needed

The other four columns have the same options which are: Submitted, Approved, Declined.

When a row is created via a form it will automatically assign an option based on who submits the form, it will either be No or Send for Concurrent Approvals. Three separate approval workflows will go out (these are the concurrent approvals) to the Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, Reg Sr. Dir Comm Sol Sales Ops Approval. There is no hierarchy here which is why it isn't one approves then it moves to the next.

I need a formula to change the cell value in the Approved column based on the following criteria:

If the value in Approval Needed is No then the value in Approved should be Yes

If the Value in Approval needed is Send for Concurrent Approvals AND the values in Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, and Reg Sr. Dir Comm Sol Sales Ops Approval are ALL Approved then the value in Approved should be Yes.

If the Value in Approval Needed is Send for Concurrent Approvals AND the values in Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, and Reg Sr. Dir Comm Sol Sales Ops Approval are ALL Submitted then the value in Approved should be Hold.

If the Value in Approval Needed is Send for Concurrent Approvals AND the values in any one of Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, or Reg Sr. Dir Comm Sol Sales Ops Approval are Approved but one or two are still Submitted then the Value in Approved should remain Hold until ALL are Approved. For instance Sr. Dir Comm Sol Sales Ops Approval is Approved but MA Dir. Comm Sol Sales Ops Approval and Reg Sr. Dir Comm Sol Sales Ops Approval are both Submitted then Approval would still show Hold.

If the Value in Approval Needed is Send for Concurrent Approvals AND the value in any one of Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, or Reg Sr. Dir Comm Sol Sales Ops Approval is Declined then the value in Approval should be No.

If after reviewing one of the approvers decides that it requires VP approval they can change the Approval Needed to VP Approval Needed which will trigger another approval workflow to go to the VP. The approved column should then follow the same basic logic

If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Submitted the Value in Approved should be Hold

If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Approved the Value in Approved should be Yes

If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Declined the Value in Approved should be No.

Any help or ideas on how to make this easier would be appreciated!

jessica.smith

Hey Christopher, Does this work for you?

I interpreted "The Approved Column (symbol) has the following options: Yes, Hold, No" as:

Red=No,

Yellow=On Hold

Green=Yes

Proposed formula:

=IF(OR([Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [MA Dir. Comm Sol Sales Ops Approval]@row = "Declined", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [VP Approval]@row = "Declined"), "Red", IF(OR([Approval Needed]@row = "No", AND([Approval Needed]@row = "Send for Concurrent Approvals", [Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved"), AND([Approval Needed]@row = "VP Approval Needed", [VP Approval]@row = "Approved")), "Green", "Yellow"))

This reads as:

The symbol will be red if any of the four columns equal "declined"

ELSE

The symbol will be green if any of the following conditions are met:

No approval needed OR

Only concurrent approvals are needed and all three directors approved OR

VP approval is needed and the VP approved

ELSE

The symbol will be yellow