How to have status in parent row change to complete when all child rows are complete.

Alicia D

Is there a way to make the parent rows change based on the child rows? For instance, I want the parent row to show as complete, once all of the rows below are complete.

Paul Newcome

@Alicia D Try this:

=IF([Percentage Complete]@row = 1, "Complete", IF([Percentage Complete]@row = 0, "Not Started", "In Progress"))

BullandKhmer

Is this in a Gantt, whereby the % Complete for the parent row auto calculates based on the % complete of the child rows?

If so you could build an automation where when % complete changes to 100% your status column automatically changes to "Complete", this is the simplest solution.

Paul Newcome

Are you wanting the rest of the status options in the parent row to be manual?

Alicia D

@Paul Newcome and @BullandKhmer , Yes it would be nice to have the other status options be automatic. Currently the percentage in the parent rows is calculated automatically based on the child rows. I like the idea of the "complete" changing when the percentage reaches 100%. How would I do this?

Paul Newcome

What are the other statuses and what would the logic be for each of them? Would it be driven by percentage on the parent row or would it be evaluating all of the child rows?

Alicia D

@Paul Newcome here are my other fields in the parent row. Percentage complete is automatically calculated based on the child rows which are entered manually. I would like for the parent rows for the "Complete", and "Status" columns to be calculated by the manually entered child rows. Start date and end date is already updating automatically from the manually entered child rows.

Paul Newcome

Right, but would you want the Complete column to update based on the child rows in the Complete column, or would you want to say something along the lines of it the percentage is zero then the status is "Not Started", if it is between 1 and 99% then it is "In Progress" and 100% is "Complete"?

Alicia D

@Paul Newcome it would be nice to have the percentage in the parent row reflect the completion. Here is the formula I am using: =IF([Percentage Complete]@row = "0%", "Not Started", IF([Percentage Complete]@row = "100%", "Complete", IF([Percentage Complete]@row < "100%", "In Progress", IF([Percentage Complete]@row > "1%", "In Progress"))))

I thought it was working, but it shows as in progress even when it is at 0%

Paul Newcome

@Alicia D Try this:

=IF([Percentage Complete]@row = 1, "Complete", IF([Percentage Complete]@row = 0, "Not Started", "In Progress"))

Alicia D

@Paul Newcome this worked, thank you!

Paul Newcome

Happy to help. 👍️