Project Progress/Reporting
I am creating an simple method to help us determine project progress to report back to our customers. The idea is to group tasks into phases and then classify each project as being in a specific phase depending on which tasks have been complete. Once all tasks in a phase have been completed, the sheet will report back that the project has advanced to the next phase.
Currently, each task on my sheet is in its own row and includes a checkbox cell to indicate task completion when checked. I have assigned each task a Phase (I, II, III, IV, or V) in a separate column. To simplify my request, I will assume each phase has 3 tasks:
Phase I: Task 13; Phase II: Tasks 46; Phase III: Tasks 79; Phase IV: Tasks 1012; Phase V Tasks 1315. I need the equations to have the following logic
 If Tasks 13 are not all complete then return "Phase I"
 If Tasks 46 are not all complete then return "Phase II"
 If Tasks 79 are not all complete then return "Phase III"
 If Tasks 1012 are not all complete then return "Phase IV"
 If Tasks 1215 are not all complete then return "Phase V"
 If all Tasks are complete then return "Ready for Contract Signature" or some other value indicating that
It should also be noted that if the tasks for a later Phase are all complete but not the current one, then the current phase should be displayed. For example, Phase II tasks 46 are complete but only tasks 1 and 2 of Phase I are complete. The status would still appear as Phase I despite the logic of Phase II's completion returning Phase III.
Best Answer

It is indeed possible. The easiest way to do this is to use the formula I posted above. adjusted for the complete status
=IFERROR(IF(Complete@row = 1, "Complete", IF(AND(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, INDEX(CHILDREN(Complete@row), MATCH("II", CHILDREN(), 0)) = 1), "III", IF(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, "II", "I"))), "")
In the parent rows of your Phase column. Then use this formula in parent rows of the complete check box.
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), =1), 1, 0)
What this does is flag the parent check box only if all of the children below it are also checked.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers

Are all your task Children rows to a parent row?
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Here is a screenshot that shows three major tasks within each overarching category:
In this instance, under "Prelim Design", that would return a value of "Phase I" on this other summary/reporting sheet. Once "ELECTRICAL" is checked, it would advance to "Phase II" and then to "Phase III" when "CIVIL" is checked and so on. The end goal will be even more granular but let's start with just the basic principle before making it extremely complex and detailed.

=IF(AND(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, INDEX(CHILDREN(Complete@row), MATCH("II", CHILDREN(), 0)) = 1, INDEX(CHILDREN(Complete@row), MATCH("III", CHILDREN(), 0)) = 1), "Complete", IF(AND(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, INDEX(CHILDREN(Complete@row), MATCH("II", CHILDREN(), 0)) = 1), "III", IF(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, "II", "I")))
This will also flag it as complete if all three are checked.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Thanks @Mark.poole! Let me further explain and seek clarification using the image below:
I understand that Rows 4145 are children of row 40 and 47 to 52 are children of 46; 40 and 46 are both children of 39. Assume that the parent task of "Interconnection" only has two children tasks which are "Preliminary Screen" and "CESIR". I would like a few things to happen, let me know if they are not possible, otherwise, please fill in the formula with the correct cell information:
 Status cell with formula in it will show "I" If any of rows 4145 are not checked
 If ALL of rows 4145 ARE checked, then the Status cell with the formula will return "II"
 Once All of rows 4752 ARE checked, then the Status cell will show "complete"
Since I'm not sure what goes in each of the functions (such as "CHILDREN()" and in place of "complete@row"), it would be incredibly helpful if you could attempt to fill in the numbers/values using the example sheet above. As a side note, I am familiar with Microsoft Excel and filling in functions using the number/letter format such as A1 or A1:B9; just not Smartsheet which uses words instead of letters to describe the column. Smartsheet also doesn't guide you as you are entering the formulas which makes it more difficult for a new user.
Thanks!

It is indeed possible. The easiest way to do this is to use the formula I posted above. adjusted for the complete status
=IFERROR(IF(Complete@row = 1, "Complete", IF(AND(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, INDEX(CHILDREN(Complete@row), MATCH("II", CHILDREN(), 0)) = 1), "III", IF(INDEX(CHILDREN(Complete@row), MATCH("I", CHILDREN(), 0)) = 1, "II", "I"))), "")
In the parent rows of your Phase column. Then use this formula in parent rows of the complete check box.
=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), =1), 1, 0)
What this does is flag the parent check box only if all of the children below it are also checked.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Thanks! @Mark.poole what (if any) values should be placed within the parenthesis of the "CHILDREN()" function? What do I put in place of "complete@row"?

I edited my formula above. to account for an error in the highest level of the Hierarchy. The rows names I chose are specifically based off the rows in your image. I tried to mimic it for the screen shot and formulas. You don't need to enter anything into the children function as it is specific to the Parent you put it in. There are ways to make both of them column formulas but that requires helper rows.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

It worked! Thanks @Mark.poole
Help Article Resources
Categories
Check out the Formula Handbook template!