Formula in parent row for updates in child rows

Hello! I have my sheet set up so that all of the update columns in the child rows are automatically a red dot. Once a row has been updated the cell turns to a green dot. It will stay green for 4 days as I have the formula set up so that on Mondays I can see if any PM's have updated the sheet since Thursday (4 days prior). I have this cell linked to our Monday Team Meeting Dashboard. Question. I have been given two different formulas for the parent row and I am not sure which one will work. Can anyone tell me the difference:

Child Rows:

=IF(Modified3 >= TODAY() - 4, "Green", "Red")

Parent Row:

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

vs.

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

Any assistance with this is GREATLY appreciated!!!

Screen Shot 2018-08-13 at 10.52.47 AM.png

Find more posts tagged with

Formulas

Project Management

Comments

Paul Newcome

Really there shouldn't be a difference. When referencing specific text I skip the =. They will both provide the same result, so it's just user preference.

EDIT:

I misread the original post. My apologies. If you want the parent to turn green as soon as at least one child has turned green, then Charles's suggestion will work.

=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", "Red")

Will leave the parent row as Red until ALL children are green.

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

What this does is... If ALL children are green, then so is the parent. If NO children are green, then the parent is red. If only SOME children are green, then the parent will be yellow. Gives you a "Complete/In Progress/Not Started" kind of tracker in the parent row.

Charles DESCOTES

Use ">= 1"

Obviously you want the parent to turn green as soon as at least one child has turned green.

If you used ">1" you would need at least 2 children to green up before the parent does.

You can be more precise to count all reds, oranges and reds:

=COUNTIF(CHILDREN(), "Red") + "?-" + COUNTIF(CHILDREN(), "Yellow") + "?-" + COUNTIF(CHILDREN(), "Green") + "?"

Or the parent could be Red if none child is green,

then the parent could move up to Yellow when some (but not all) children are green,

and eventually the parent is Green when al children are green:

=IF(COUNTIF(CHILDREN(), "Green") = 0, "Red", IF((COUNTIF(CHILDREN(), "Green") / COUNT(CHILDREN())) = 1, "Green", "Yellow"))

* re. COUNT(CHILDREN()), it does only count children with a value in the same column, but if you change it to COUNT(CHILDREN([Task Name]x))

where Task Name is the name of the main column, always addressed and x is the row of the parent where you have the formula you can avoid being tricked by an empty color stop in one child.

Clear enough ?

KatieTMBA

Thanks Paul!

That got me to the formula that I needed - simple and easy.

=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green",

IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue",

IF(COUNTIF(CHILDREN(), "Red"), "Red",

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

If there are any red, parent row turns red. If all are green or all are blue it turns green or blue, respectively. If it's a mix of colors (other than red) the parent row returns yellow.

Thanks much!

-Katie

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?