Multiple if statements combined into 1

Options

Hi,

I am trying to create a formula to update the status column.

If ALL 5 people columns has any combination of "approved", "approved: with questions", "approved: with comments", then it marks the status Approved.

If any of the 5 people columns has "In Review" or Reviewed: with questions", the status column in mark as "In Review".

I am not sure how to build this the most efficient way.

Answers

  • KryF
    KryF ✭✭
    Options

    Hi, you can try the steps below. Not sure if it works effectively, just a suggestion.

    1) Put new column "Approval status" in the sheet

    2) Create automation "Change cell value"

    3)Inside automation workflow, consider "When rows are added or change" > when "Any Field" changes .....

    4) Then, add "condition to filter row" where "Supervisor" column "is one of" Approved, approved with comments etc. -- "Add another condition" --and where "Manager" column "is one of" Approved, approved with comments etc.. and so forth

    5) Change cell value "Select a column: Approval status" to Approved

    6) Repeat to set automation for Review status.

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

    Hi @Stephanie Tran

    I hope you're well and safe!

    Try something like this.

    =
    IF(COUNTIF(Supervisor@row:VP@row, CONTAINS("Approved", @cell)) = 5, "Approved", 
    IF(CONTAINS("Review", Supervisor@row:VP@row), "In Review"))
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    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 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭
    Options

    Thank you both for options.

    @Andrée Starå your formula worked.

    It is has saved me so much time.

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

    @Stephanie Tran

    Excellent!

    You're more than welcome!

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Stephanie Tran
    Stephanie Tran ✭✭✭✭
    Options

    @Andrée Starå

    I would like to add another thing to this formula and can't seem to get the syntax correct. I added another column [Public] that is a check box option.

    I would like my status column to change from approved to public if that box is check.

    =IF([Public ]@row = 1, "Public")

    Can I or how do I add that to my existing formula?

    =IF(COUNTIF(Katrina@row:Maria@row, CONTAINS("Approved", @cell)) = 5, "Approved", IF(CONTAINS("Review", Katrina@row:Maria@row), "In Review"))


    I hope this makes sense and thanks for any help.


    Steph

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!