"Status" column to reflect 2nd column input for "Cancelled or On Hold"
I have a Status column that is calculated via an IF statement referencing two other columns, [% Complete] and [Cancelled / On Hold].
=IF([% Complete]@row = "", "", IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))))
I would like the roll-up status of the parent to say "Cancelled / On Hold" if all Status fields are "Cancelled" or "On Hold" instead of "In Progress" as it does now.
Is that possible and what would I need to add to my formula to do so?
Thank you kindly.
Answers
-
Hi @jbonetti
Try the following:
=IF([% Complete]@row = "", "", IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold") = COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"), IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))
I'll break it down:
=IF([% Complete]@row = "", "",
This is the same as your current formula. Then we get into the question of hierarchy:
IF(COUNT(CHILDREN([Task Name]@row)) > 0,
If there are Children below this row (checking the Task Name to identify the number of children, since blank cells wouldn't be counted)
Then, if the COUNT of "Cancelled" and "On Hold" is the SAME as the Count of ALL Children, return "Cancelled / On Hold"
IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold")
=
COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"),
Finally, otherwise, the rest of your formula.
IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!