Nested IF Formula for Automating Related Columns

I have a sheet that has rows added through an automated background process (not a form). As the rows come in, they are assigned a date in a column called "Date of Inquiry." There are two other pertinent columns in the sheet: a Harvey Ball column called "Status" and a dropdown-menu column called "Outcome." The Harvey Balls are RYGG and the Status options are In Progress, Overdue, Referred, Resolved and Abandoned.

I'm looking to automate two things.

First, in the Outcome column, I would like the default option when a row is added to be "In Progress." If a user does not manually change the Outcome to Resolved, Referred or Abandoned within 14 days of the Date of Inquiry, I would like that column to change to Overdue.

Second, I would like the Harvey Balls to correspond to the Outcome column.

For the Harvey Ball automation, I have this formula:

=IF(Outcome@row = "abandoned", "Gray", IF(Outcome@row = "In Progress", "Yellow", IF(Outcome@row = "Resolved", "Green", IF(Outcome@row = "Referred", "Green", IF(Outcome@row = "Overdue", "Red")))))

This is working fine. But, I am not sure how to manage the desired automation in the Outcome column

Tags:

Answers

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

    Hi Danielle,

    The third-party service, Zapier, is an excellent option for this scenario. Is that an option for you?

    Would that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post 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 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.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    @Andrée Starå - thank you for replying. No, Zapier is not an option for me given our InfoSec restrictions.

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

    Ok.

    The issue is that as soon as you change the value of a cell with a formula manually, it will remove the formula.

    To make it work, you would need to account for all conditions except the manual change, but the problem is that the formula auto-fill feature would break then as well.

    Make sense?

    It might be possible to structure a solution with the help of so-called helper columns.

    Would that work?

    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.

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    I am okay with manual entries removing the formula, because the only two outcomes I want to automate are "In Progress" (as the default) and "Overdue" (which can happen only if a date has passed and no one has manually overridden with another status). So, my challenges are: (1) how do I get the "In Progress" entry as a the default, (2), how do I get that to convert to "Overdue" if the date comes without change to another status, and (3) how do I ensure that the formula continues to populate down-column if an intervening cell has a manual change?

    I think I can solve #1 by entering ="In Progress" in each column down the cell. And I think #2 is solved by some kind of IF/OR formula conditioned on the date. Not sure how to craft that. But, I am really unsure about #3.

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

    Ok.

    #3. Would it work if you moved the rows and have two placeholders with the formula structure in the bottom?

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!