Nested IF Formula for Automating Related Columns

Danielle Arteaga

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

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!

Danielle Arteaga

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

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?

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å

Ok.

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