How to update a parent status based on a child status.

damon.tackett

I want to be able to update the status of a parent row based on the status of the child row.

Currently I have dropdowns set up for "Active", "Inactive" and "Pending". I am hoping to find a formula that when I select "Active" for ANY of the children under that parent it changes the status to Active. Otherwise if ALL children are "Inactive" or "Pending" the parent would reflect the appropriate status.

There would only ever be a combination of "Active"/"Inactive". The "Pending" status is a holder under that parent row is evaluated at the first of the month and then all of the children are updated to "Active" or "Inactive"

Kimberly Loveless

You can use a formula but it could be long depending on how many children it has, when using the phrasing Active/Inactive, and it could have to be edited for each parent. If the options were something like In Progress/Inactive/Pending you could use the formula below ...

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(CONTAINS("Pending", CHILDREN()), "Pending", "Inactive"))

the reason this wouldn't work for Active/Inactive is because Inactive has active within it and it causes the parent row to show an Active status. (see below)

damon.tackett

Thank you Kimberly that makes sense. I will play with that formula and see what I can come up with.

Edit - so it seems this won't work as a column formula? Which I guess makes sense. I was just trying to avoid having to past it in each parent column individually.

Mike TV

@Kimberly Loveless @damon.tackett

I've been trying to work on this in my free time today but am not quite there yet. The formula Kimberly provided isn't working for me because if there are Child rows with Active it still marks them as Inactive and if there are only some Inactive it still marks the parent as Inactive.

If I get this working I'll update you.

damon.tackett

@Mike TV i think that is, as @Kimberly Loveless pointed out, the word Inactive contains "active" in the word. For my purposes i changed Inactive to "Cancelled" and it allowed the children to update the parent appropriately, but I can into another problem in that I have to add that formula to each parent instead of being able to change it into a column formula. When i change it to a column formula it seems to make everything "Cancelled"