I am trying to look across 4 columns to pick out if anyone Denied a Access Request

01/14/21
Accepted

With this formula below I can see if each of the 4 people Approved something, but how can I add a nested section that would also mark a [Approval Check] field "Someone Denied The Request" if any one of the 4 approvers mark the approval as "Declined"

=IF(AND([Bob Approval]@row = "Approved", [Jack Approval]@row = "Approved", [Jill Approval]@row = "Approved", [Matt Approval]@row = "Approved"), "All Approved", "Pending Approval")

Best Answer

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    Accepted Answer

    Hi @MJ215

    Try something like this.

    =IF(COUNTIF([Bob Approval]@row:[Matt Approval]@row, "Approved") = 4, "All Approved", IF(CONTAINS("Declined", [Bob Approval]@row:[Matt Approval]@row), "Request Declined", IF(CONTAINS("Pending", [Bob Approval]@row:[Matt Approval]@row), "Pending Approval")))

    Did that work?

    I hope that helps!

    Be safe and have a fantastic day!

    Best,

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

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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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 about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Answers

  • KDMKDM ✭✭✭✭✭

    Hey @MJ215

    I need some clarification please. Are you asking for an IF condition to build onto your current IF to take into account any denied request, as well as a new formula in the checkbox column which triggers if this Approval summary column has Declined? Either/both requests are very doable, I'm just unclear what you're asking. While I'm asking, are the four approval columns all side by side as four continuous columns, or are the spread out in your sheet? This helps determine which functions are possible.

    Kelly

  • One IF statement expanding on what I have would be great. I would want the [Approval Check] column to hold a formula to say if the approval is still pending with anyone and hasn't been marked "Declined" it should be marked "Pending Approval", once someone declines the approval I would like the [Approval Check] column to be marked "Request Declined" and that will trigger an email. When all 4 people approve the [Approval Check] will be "All Approved" and that will trigger a different email.


    My current formula can only check to see if someone marked "Approved" so even if the approver marks "Declined" the [Approval Check] is still "Pending Approval"


    =IF(AND([Bob Approval]@row = "Approved", [Jack Approval]@row = "Approved", [Jill Approval]@row = "Approved", [Matt Approval]@row = "Approved"), "All Approved", "Pending Approval")


Sign In or Register to comment.