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!!!
Comments
-
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.
-
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 ?
-
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
-
Help Article Resources
Categories
Check out the Formula Handbook template!
