Automatically update the progress based on blank/not blank cells

saturnrings

Hi, everyone,

I'm trying to figure out how to write this with my simple knowledge of using formulas, and I'm stuck.

What I want:

I want to update the 'Progress' based on the blue columns.

 

If all are empty - Not Started

If some are empty - In Progress

If some are empty and the due date(Target End Date) is reaching - At Risk

If all are filled - Completed

For the 'Status' column, I tried to create automation rules for all 4 conditions. However, only 'Completed' workflow is working. I guess there is a conflict that I can't spot..

So instead of using automation, I decied to try using 'IF' and 'And' as below, to update 'Not Started' & 'Completed' in the 'Progress' column, which turns out fine.

=IF(AND([Needs Discussion]@row <> "", A@row <> "", B@row <> "", C@row <> "", D@row <> "", E@row <> "", F@row <> "", G@row <> "", [ISSUES1]@row <> "", [ISSUES2]@row <> "", [ISSUES3]@row <> "", SUMMARY@row <> "", Notes@row <> "", [Reason (Parent level only)]@row <> ""), "Completed", "Not Started")

However, I don't know how to write formulas for 'In Progress' & 'At Risk' into the same cell, in order to convert it into a Column Formula. I'm pretty much a novice in using formulas and all I can think of is to use another 'IF(OR...' right after the one above,... which didn't work.

Much appreciated if anyone could help me figure why this isn't working and redirect me to the right direction. Thank you!

 

saturnrings

Hi,, I kinda figure this out :D but please let me know if you have a better way to do this..

I made 2 different columns, one is to check the date value(At Risk, Past Due, On Track), and the other is to check if the cells are empty or not(Not started, in progress, completed).

And then, combine those 2 columns values into another 'Status' column, then let the 'Health'(symbols) column to refer to the 'Status'.

So far, looks like it's working as I wanted.