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
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!