How do I make a column formula for parent/child RYGB balls?

Emily McNeeley

I need a formula to do the following for the Child row (note: I do have a Child Count Column set up):

Contract Expiration Date is in the past = Red

Contract Expiration Date is in the next 120 days = Yellow

Contract Expiration Date is more than 120 days out = Green

Contract Expiration Date is BLANK = Blue

I then need the parent to display in the following order based on what appears,

If any are red, display Red

If there are no red, but there are yellow, display Yellow

If there are no red or yellow but there are blue, display Blue

If there are no red or yellow or blue and all are green, display Green

This is currently what I have as the column formula but it won't work now that I have added in Parent/Child relationships

=IF([Contract Expiration Date]@row < TODAY(), "Red", IF(AND([Contract Expiration Date]@row >= TODAY(), [Contract Expiration Date]@row < TODAY(+120)), "Yellow", "Green"))

Purnima Gore

Hi @Emily McNeeley

I have found a method that can work. You do need a different formula depending on whether it is a parent or child, however, by using the children function that should work. I used 30 days but you can easily change the day to whatever you want.

Parent Row Formula

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))

Child Formula

=IF(ISBLANK([Contract Expiration Date]@row), "Blue", IF(AND([Contract Expiration Date]@row >= TODAY(), [Contract Expiration Date]@row < TODAY(+30)), "Yellow", IF([Contract Expiration Date]@row < TODAY(), "Red", "Green")))

I did try and create a formula that would enter the symbol row depending on if the item was a parent or child but that was causing circular references.

Hope that helps.

ATB

Purnima

Purnima Gore

Hi @Emily McNeeley

I have found a method that can work. You do need a different formula depending on whether it is a parent or child, however, by using the children function that should work. I used 30 days but you can easily change the day to whatever you want.

Parent Row Formula

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))

Child Formula

=IF(ISBLANK([Contract Expiration Date]@row), "Blue", IF(AND([Contract Expiration Date]@row >= TODAY(), [Contract Expiration Date]@row < TODAY(+30)), "Yellow", IF([Contract Expiration Date]@row < TODAY(), "Red", "Green")))

I did try and create a formula that would enter the symbol row depending on if the item was a parent or child but that was causing circular references.

Hope that helps.

ATB

Purnima

Emily McNeeley

Thank you @Cierr - This is very helpful and works well. I wish it worked as a column formula but appreciate your help with this!