Automatically Update Grandchild Status when Child Status Changes

Sheryl Sheldon

Hello,

I feel like I'm making this formula much more difficult than it needs to be (I'm totally overthinking it!). Anyway, I have a sheet that has Grandparent, Child and Grandchild rows. When the Child status changes to "closed", I want the status for the grandchild to update to "closed" Does anyone know any easy formula to do this? Thank you!

Rich Coles

Hi @Sheryl Sheldon - something like this?

I always like to use column formulas where I can, so there are two options here.

1. In the status column if you are happy to add in a formula below the parent status, then you can simply use =IF(PARENT() = "Closed", "Closed", "") and then overwrite it if needed etc
2. Given the inherent risk of that formula not being there / being deleted etc, could you have a master status column for the grandchildren? If so, add in a Level column with this formula =COUNT(ANCESTORS(Item@row)) and then in the Status column formula use this =IF(Level@row = 2, IF(PARENT(Status@row) = "Closed", "Closed", ""))

Hope that helps… a lot depends on how you have your sheet set up more widely and how people will be updating the rows as that will influence the solution you use.

Sheryl Sheldon

Hi @Rich Coles

That seemed to work when I added a Level column. Thank you!
Sheryl

Rich Coles

Great! Glad I could help and glad you're using the Level column… I add that to every sheet I put hierarchy in. It's particularly useful for conditional formatting too! Please do mark this as answered. Thanks!