Automatically update the progress based on blank/not blank cells
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!
Answers
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives