Auto Populating Columns

ejacksonBPT

Hello,

I populated the Risk Columns and Task Health Column in this example manually. Is there a way to accomplish this automatically based on the Internal/External Column, Due Date and Status Columns?

Additionally, is there a way to remove the Risk flags from the header row in green?

For instance:

If I select "Complete" in Row 2 can the Task Health automatically populate the GREEN symbol?

If I select "In Progress" in Row 3 AND the Due Date is passed due can the Task Health automatically populate the RED Symbol AND since this Row is marked "Internal" can the Internal Risk column be automatically checked?

Thank you.

Paul Newcome

You would use formulas for this. IF and nested IF formulas should do what you need.

What is the logic for the rest of the colors / scenarios?

ejacksonBPT

Hello Paul,

This is a big list and I super appreciate your help:

internal > In Progress > Day's actual date is BEFORE due date > make the Task Health Green

internal > Pending > Day's actual date is BEFORE due date > make the Task Health Green

Internal > Completed > make the Task Health Green

internal > In Progress > Day's actual date is AFTER due date > make the Task Health Red > check the internal Risk Flag

internal > Pending > Day's actual date is AFTER due date > make the Task Health Red > check the internal Risk Flag

internal > In Progress > Day's actual date is two days before due date> make the Task Health Yellow > check the internal Risk Flag

internal > Pending > Day's actual date is two days before due date> make the Task Health Yellow > check the internal Risk Flag

And all the same scenarios for external and external rick column

Thank you!!

Paul Newcome

Try this:

=IF(OR(Status@row = "Complete", [Due Date]@row >= TODAY(2)), "Green", IF([Due Date]@row < TODAY(), "Red", "Yellow"))

The above would be for the Status column. Then flagging the risk column would be an IF/AND along the lines of

=IF(AND([Internal/External]@row = "Internal", [Task Health]@row = "Red"), 1)