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.