Automatically update the progress based on blank/not blank cells
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!