Parent/Children Formula

lwells

I am trying to have a formula look at a "Parent" row Status IF it is blank than to then look at the child row associated and if the Status is Red, Green or Blue, place status within Parent Status ONLY, if it is not any of the listed status leave blank.

I have the below formula, but it keeps throwing a #circular ref error. Can anyone help me figure out which part is the error?

Brian_Richardson

When you say "look at the child row associated" - it looks like you have multiple child rows with red, green, or blue status. Which child row do you want to pull status from? The last one? Or do some calculations like "If there's any red make the parent red, if there's 2 or more yellows make the parent yellow" etc.?

Also double checking - I see you have defined rows as "Parent" or "Child" in the Row Type, but is there actually parent/child hierarchy (indent/outdent) applied to the rows to match your Row Type setting?

lwells

It needs to pull on the most current date for the status "last child row". Yes, I have the Parent/Child rows indent/outdent for the parent/child hierarchy.

Example: The Parent Status should represent the current most status the last data entry/last line and/or most current Start Date

I appreciate any help :)

Brian_Richardson

On the Parent row, in the Status column, you can enter this formula which will pick up the last child's status

=INDEX(COLLECT(CHILDREN(), CHILDREN(), OR(@cell = "Red", @cell = "Blue", @cell = "Green")), COUNT(COLLECT(CHILDREN(), CHILDREN(), OR(@cell = "Red", @cell = "Blue", @cell = "Green"))))

This basically says "give me a set of all the statuses of the children of this row where they are Red, Blue, or Green. Then, select the one from the list that equals the count of those statuses…so basically the last one on the list.

You'll need to enter this formula individually into the Parent row Status cells.

Alternatively you could create a "Parent Status" column and use this formula as a column formula so that it will always work. For that column formula, alter it slightly to be this:

=IF([Row Type]@row="Parent",INDEX(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), OR(@cell = "Red", @cell = "Blue", @cell = "Green")), COUNT(COLLECT(CHILDREN(Status@row), CHILDREN(Status@row), OR(@cell = "Red", @cell = "Blue", @cell = "Green")))))

lwells

Thank you Brian,

I will let you know it works. I really appreciate your help.