Nested COLUMN FORMULA for PARENT and CHILD

Geoff Baker

How do I control the Parent and Child with different formulas?

This is the column formula I currently have but I ONLY want it to apply to the CHILD

=IF(AND([MTO QTY]@row > [ORDER QTY]@row, [IS CHILD]@row = 1), "Red", "Green")

THEN for the PARENT, I want it to look at all the CHILDREN and if ANY is RED then the PARENT should show RED.

IF all CHILDREN are GREEN then the PARENT shows GREEN. 

Assuming I would need an additional IS PARENT but not sure how to format it.

Geoff Baker

Paul Newcome

Once you have figured out your two separate formulas, this is how you would combine them...

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

Geoff Baker

Ok that makes sense not how to reference the two formulas.

I got it one step closer.

=IF((COUNT(CHILDREN()) = 0, ([MTO QTY]@row > [ORDER QTY]@row, [IS CHILD]@row = 1), "Red", "Green"), (CONTAINS("Red", CHILDREN()), "Red"))

But now I am getting a syntax error.

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, IF(AND([MTO QTY]@row > [ORDER QTY]@row, [IS CHILD]@row = 1), "Red", "Green"), IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", "Green"))

Geoff Baker

Amazing Paul thank you!

It works perfectly.

Cheryl Hishmeh

Thank you for this explanation, QUESTION: I have a field that indicates it is the parent row. Header is the column name. It is a check box. So I am trying to get this formula working is giving me an error.

=if(heading@row=1,[Group Attendance]@row + 1,if(complete@row=0,Parent(@column)))

I want to show the total credits for attendance in the parent row field, and carry this value from the parent field in each of the fields below. Can I reference the parent row in this column formula?

Paul Newcome

@Cheryl Hishmeh Are you able to provide some screenshots for context?

Cheryl Hishmeh

Paul Newcome

@Cheryl Hishmeh I'm still not sure I follow exactly what you are trying to do.

MarieM

@Paul Newcome Could you please help me with a formula that will change the Parent cell based on the content of the children cells?

I'm referring to the "Attn" column in the below sheet. I would like the following logic to be followed:

IF 1 child = "Assigned", Parent = "1 Assigned"

IF 2 children = "Assigned", Parent = "2 Assigned"

IF 1 child = "Review Received", Parent = "1 Review Received"

IF 2 children = "Review Received", Parent = "2 Reviews Received"

and so on...

Paul Newcome

@MarieM What if there is 1 assigned and 1 review received or any other combination?

MarieM

@Paul Newcome some designations would take precedent over others (just based on our workflow). For example if 1 review is received that automatically means both have been assigned, so we wouldn't need to distinguish that. We wouldn't need the parent to change for all possible combinations, but just specific changes/combos. does that make sense?

I guess if none of the specified combos are met it would be "in queue" for example

Paul Newcome

@MarieM In that case, what are all of the various scenarios and how would you want them output? You have indicated that "Review Received" takes precedence over "Assigned", but are there any other statuses that would need to be accounted for?

MarieM

I have a workflows that set it to different items on the drop down menu: "In Queue" when it arrives on the sheet, another that marks it as approved and removes it from the sheet when it's approved.

Actually, the more I think about it, It might be better to just count the status types of each child (there is a max of 2 children for each parent):

There are 3 options for the status of children in the dropdown menu: "Assigned", "Review Received", "Late Review". I could just count how many of each are present in the children (ie. "1 Late; 1 Received") or "2 Assigned".

Would I need a helper column if I wanted the Parent to have the above described formula and the children to have the option to manually select from the dropdown?

Paul Newcome

You wouldn't need a helper column. You would just put the formula in the parent rows. Try something like this:

=COUNTIFS(CHILDREN(), @cell = INDEX(DISTINCT(CHILDREN()), 1)) + " " + INDEX(DISTINCT(CHILDREN()), 1) + IFERROR("; " + COUNTIFS(CHILDREN(), @cell = INDEX(DISTINCT(CHILDREN()), 2)) + " " + INDEX(DISTINCT(CHILDREN()), 2), "")