Parent status based on children
This gets asked all the time and I've looked through posts but I can't get the formulas to work for my project.
I have a project with hierarchy 1,2 and 3. I need the parents' status to update based on the children. My status options are: Not Started, In Progress, Complete and Blocked.
If one child is in progress or complete the parent should show in progress, if one child is blocked the parent should show blocked, if all children are complete the parent should show complete, if all children are not started the parent should show not started.
Answers

You need a nested if formula
=if(contains("Blocked", children()),"Blocked", if(contains("In Progress", children()),"In Progress", if(contains("Completed", children()),"Completed", "Not Started")))

This would be the formula to use in the Parent Row of the Status column. This will not be able to be a column formula though. If you want to be able to use it as a column formula, I would ask do your child rows currently use a formula, and can you share that?
=IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Blocked"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "In Progress"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Not Started"), 1, 1)), IF(ISERROR(INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), "Complete"), 1, 1)), "", "Complete"), "Not Started"), "In Progress"), "Blocked")

Right, this can't be a column formula. But this formula is giving me a similar result to others I've tried. If one of the children is marked complete, the parent reverts back to not started, the only way to have the parent show in progress is if the children show in progress, when they go to complete the parent goes back to not started. Everything else works, i.e. a blocked ancestor will change the parent to blocked, an in progress ancestor changes the parent to blocked, if all the children are complete the parent goes complete. So it's just stll the problem that if one ancestor shows complete the parent does not show in progress it shows not started. See the screenshot, the parent should show in progress if at least one child is complete but it does not.

Which formula did you use? In my testing which I tested all the options, I did not have any issues. And also what is your structure here? If you have multiple hierarchies that would change the formula. Can you share the hierarchy levels
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!