Project Progress/Reporting

DC GSS

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 1-3; Phase II: Tasks 4-6; Phase III: Tasks 7-9; Phase IV: Tasks 10-12; Phase V Tasks 13-15. I need the equations to have the following logic

• If Tasks 1-3 are not all complete then return "Phase I"
• If Tasks 4-6 are not all complete then return "Phase II"
• If Tasks 7-9 are not all complete then return "Phase III"
• If Tasks 10-12 are not all complete then return "Phase IV"
• If Tasks 12-15 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 4-6 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.

Mark.poole

@DC GSS

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.

Mark.poole

@DC GSS

Are all your task Children rows to a parent row?

DC GSS

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.

Mark.poole

@DC GSS

=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.

DC GSS

Thanks @Mark.poole! Let me further explain and seek clarification using the image below:

I understand that Rows 41-45 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:

1. Status cell with formula in it will show "I" If any of rows 41-45 are not checked
2. If ALL of rows 41-45 ARE checked, then the Status cell with the formula will return "II"
3. Once All of rows 47-52 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!

Mark.poole

@DC GSS

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.

DC GSS

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"?

Mark.poole

@DC GSS

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.

DC GSS

It worked! Thanks @Mark.poole