Parent Formula to display modified child status

Cuatro

I am looking for a Parent / Child formula to capture a status change (R / Y / G). The Parent status will change based on the Child status Change.

For example:

If I change the status of "Software" to Red (R) in the child row, it should change the Parent status to Red.

The parent row captures any child row that changes from Green to Red and vice versa.

Any help would be awesome. Thank you!

Cuatro

Andrée Starå

Hi Cuatro,

Do you want to change the parent to the value with the highest count from the children?

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Cuatro

Andree,

Thank you for the response.

I have added a screen shot and text to explain. Let me know if you need more.

Go win,

Stanley

Andrée Starå

Happy to help!

Try something like this.

Uncertain this is what you need. This formula will change the parent to red if there is any red in the children and otherwise it will show as green.

=IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; "Green")

The same version but with the below changes for your and others convenience.

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Green")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Cuatro

Andree,

It did work! Thank you.

Can you elaborate what your formula is actually doing? I see high potential to utilize this formula; however, explaining how it operates...I would fail.

Go win,

Cuatro

Paul Newcome

Here's a breakdown of the formula for you...

 

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Green")

.

COUNTIF(CHILDREN(), "Red")

This counts how many children rows are "Red".

.

=IF(COUNTIF(CHILDREN(), "Red") > 0

If that count is greater than zero (meaning there is at least one "Red")

.

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red",

Turn this particular cell to "Red".

.

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Green")

Otherwise (there are no reds aka all child rows are green), turn this cell to "Green".

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Cuatro

Thank you!

Cuatro

Paul and Andree,

Thank you for your help!

I was wondering if you can incorporate 3 colors, not just Red and Green.

Cuatro

Paul Newcome

Yes. We would just need to know the priority and triggers for each color.

 

Cuatro

The formula you provided works and works well.

The priority is in this order.

• Parent row will display "Red" when any children are "Red"
• Parent row will display "Green" when all children are "Green"
• Parent row will display "Yellow" when any children are "Yellow" AND "No RED"

Paul Newcome

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green"))

 

Give this a try...

Cuatro

Paul, thank you. Unfortunately, it gave me an unparseable.

Any recommendations?

Paul Newcome

Yes. I recommend that I pay closer attention to what I am doing. Sorry about that. Here. Try this...

 

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "Green"))

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Karisa Ely

Hello,

I'm trying to do something similar. I have 4 cells that should trigger the Close cell to change color. The Close cell should stay RED unless ALL 4 cells are GREEN.

I can get the formula to show Green or Red but it will not change based on the 4 cells. What am I missing?

Formula 1:

=IF(COUNTIF(CHILDREN([2]1:[5]1), "Green") = COUNT(CHILDREN([2]1:[5]1)), "Green", "Red")

Formula 2: Gets Close to be Red, but will not stay red until all cells are Green.

=IF([2]1 = "Green", "Green", IF([3]1 = "Green", "Green", IF([4]1 = "Green", "Green", IF([5]1 = "Green", "Green", "Red"))))