Keep Health Status Formula When New Row Created

Devin C.

I am really close getting my health status formula that is based on both Status and Date to work. I need the formula to stay when a new row is added. I also want the status to be GREEN with DUE DATE = TODAY.

Here is what I am trying to have happen

If the DUE DATE is in the past turn the health symbol "Red"

If the DUE DATE is within 3 days of TODAY turn the health symbol "Yellow"

Current formula has TODAY yellow and it should be green

If the DUE DATE is greater than TODAY(3) turn the health symbol "Green"

 If the STATUS is NOT STARTED, COMPLETE, or POSTPONED turn the health symbol to "Gray"

If the STATUS is AT RISK turn the health symbol "Yellow"

If the STATUS is IN PROGRESS turn the health symbol "Green"

 Need to have the formula stay when a new row is added. Currently it doesn't stay when I add a new row.

=IF(OR(Status@row = "Not Started", Status@row = "Complete", Status@row = "Postponed"), "Gray", IF(AND([Due Date]@row >= TODAY(4), Status@row = "In Progress"), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow")))

Paul Newcome

If you right click on a cell containing the formula, there should be an option down at the bottom of the menu to convert to a column formula. This should allow you to not have to worry about the auto-fill conditions.

Paul Newcome

Your requirements are a little conflicting...

In one point you say that if it is within 3 days then it should be yellow, but then you also say that if it is today then it should be green. Do you want it to go green when it is more than three days in the future, then turn yellow for 3, 2, or 1 day in the future then back to green when it is today?

Devin C.

Thanks for the feedback Paul. I could see how that may seem confusing. I went back and tested this formula again and based on some other conditional formatting I have already set up I am actually ok with the Health color being yellow when Due Date equals Today and the 3 days leading up to the DUE DATE. So we can disregard that change.

I do however still have an issue when I add a new row that the formula doesn't stay in the Health Column.

Devin C.

After looking at many more discussion threads I found out what triggers a formula.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

Paul Newcome

If you right click on a cell containing the formula, there should be an option down at the bottom of the menu to convert to a column formula. This should allow you to not have to worry about the auto-fill conditions.

Devin C.

Thanks Paul. I didn't know that. I appreciate the insight.

Paul Newcome

Happy to help. 👍️