Changing a cell value based on multiple criteria

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!

Best Answer

  • jessica.smith
    jessica.smith ✭✭✭✭
    edited 06/19/23 Answer ✓

    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

Answers

  • jessica.smith
    jessica.smith ✭✭✭✭
    edited 06/19/23 Answer ✓

    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

  • Hollie Green
    Hollie Green ✭✭✭✭✭

    The below should work it will also say hold as well if any of the approval columns are blank and the Approval Needed is VP Approval Needed or any of the Approval columns are blank except the VP Approval column and the approval needed is not NO.

    =IF([Approval Needed]@row = "No", "Yes", IF(OR([SR Dir Comm Sol Sales Ops Approval]@row = "Declined", [MA Dir. Comm Sol Sales Ops Approval]@row = "Declined", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Declined", [VP Approval]@row = "Declined"), "No", IF(OR([SR Dir Comm Sol Sales Ops Approval]@row = "Submitted", [MA Dir. Comm Sol Sales Ops Approval]@row = "Submitted", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Submitted", [VP Approval]@row = "Submitted"), "Hold", IF(AND([SR Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Approved", [VP Approval]@row = "Approved"), "Yes", IF(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 Sales Ops Approval]@row = "Approved"), "Yes", IF(AND([Approval Needed]@row <> "No", OR([SR Dir Comm Sol Sales Ops Approval]@row = "", [MA Dir. Comm Sol Sales Ops Approval]@row = "", [Reg Sr. Dir Comm Sales Ops Approval]@row = "")), "Hold", IF(AND([Approval Needed]@row = "VP Approval Needed", OR([SR Dir Comm Sol Sales Ops Approval]@row = "", [MA Dir. Comm Sol Sales Ops Approval]@row = "", [Reg Sr. Dir Comm Sales Ops Approval]@row = "", [VP Approval]@row = "")), "Hold")))))))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Christopher Pretty

    I hope you're well and safe!

    Have you explored using Workflows instead?

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Christopher Pretty
    Christopher Pretty ✭✭✭✭✭
    edited 06/21/23

    This worked (I think I was just overthinking it) except for the symbols. I too thought of Red, Green, and Yellow at first except it actually was Yes, No, and Hold. Once I changed to those values it worked perfectly. Thank you.



  • Christopher Pretty
    Christopher Pretty ✭✭✭✭✭

    Unfortunately it doesn't look like it is an option to change a cell value in a symbol column using a workflow.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!