Parent/Child Status

ANTHONY MARELLI

Hello,

I'm trying to get a setup going in my project templates where the parent cell will update based off the input of of the children cells. For example, the main parent is "Onboarding", with children "Licensing", "Credentialing", "DEA". Then, each of those children has their own children to track individual providers. Is there a way to have the sub-parent cells (my terminology may be incorrect) reflect the overall status of their children, and then have the main parent cell reflect the sub-parent cells? So, each children cell has options for "Not Started", "In progress", "Not Applicable", "Complete", and "Potential/Upcoming". I'd want the parent cells to reflect "Not Started" if all children show "Not Started", "In progress" if any of the children have anything other than "Not Started", and "Complete" if all children show "Complete".

I hope this makes sense and I appreciate any advice!

Genevieve P.

Hi @ANTHONY MARELLI

Yes! You can use the CHILDREN function in a NESTED IF statement in order to create a formula to calculate this rollup for you in each parent row.

You would want to count the child rows using COUNTIF(CHILDREN()), then compare that count with each status to see if it's the same number or not.

Try something like this in each Parent row:

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "Not Applicable"), "Not Started", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Not Applicable"), "Complete", "In Progress"))

You'll notice that I also added in the status of "not applicable" so that if two are Complete and one is Not Applicable it will still say "Complete".

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @ANTHONY MARELLI

Yes! You can use the CHILDREN function in a NESTED IF statement in order to create a formula to calculate this rollup for you in each parent row.

You would want to count the child rows using COUNTIF(CHILDREN()), then compare that count with each status to see if it's the same number or not.

Try something like this in each Parent row:

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Started") + COUNTIF(CHILDREN(), "Not Applicable"), "Not Started", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Not Applicable"), "Complete", "In Progress"))

You'll notice that I also added in the status of "not applicable" so that if two are Complete and one is Not Applicable it will still say "Complete".

Let me know if this works for you!

Cheers,

Genevieve

ANTHONY MARELLI

@Genevieve P it works! Thank you SO much! I have spent literally hours trying to figure this out - turns out, I was really overcomplicating it :). Thank you, thank you, thank you! This will make my tracking SO much easier!

Genevieve P.

Happy to help! So glad to hear it worked for you! 🙂