Automate RYGB and Children together
I have a standard project sheet. I added one column called Today that in Row1 links to a separate sheet that will be triggered everyday so that it gets Today's date (this gets you around using the Today function which only triggers when a sheet is opened), every row below that first row then has a =CellReference the cell above so that as rows get added to a sheet the calculation populates in new row, allowing a calculation in the RYBG column to populate as well and always have the Today date to use in the same row. (PS must have it as a data column but not restricted).
The rest is standard Task name, Start, Finish, % complete and then my RYBG column. Blue = 100% complete, If row not 100% then: Yellow = finish date equal to or within 2 days of Today, Red = finish date less than today (so missed it), Green = Finish date not missed and have more than 2 days yet.
I have this equation made: it almost works except having trouble with Green because the equation will yield a negative number 1  XX as having that many days yet.
=IF(Finish36 = "", "Error", IF([% Complete]36 = 1, "Blue", IF((Today36  Finish36) = 0, "Yellow", IF((Today36  Finish36) = 1, "Yellow", IF((Today36  Finish36) = 2, "Yellow", IF(Today36 > Finish36, "Red", IF((Finish36  Today36) > 0, "Green", "NA")))))))
Now I also want to have it apply logic to the parent row. Meaning if you use the formula above you can have a green parent row with all red children. I want the parent to be the color of the children based on the number of children a given color. So I use this equation:
IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))
So question now is:
1. how to make the first equation work properly for Finish  Today equaling a negative number and not breaking the rest of the equation (have a feeling easy fix but been looking at too many equations)
2. marry equation 1 and 2 together so that the RYBG column has the equation all the time and populates if rows are added. PS: equation 2 doesn't address Blue at all because equation 1 would turn each row blue and if all blue then parent turns blue on that same equation and if anything not blue I don't want the parent ever showing Blue because Blue means 100% done.
So who can help? Happy to share the sheet also if anyone want it.
Thanks
Garry
Comments

I believe I now have equation 1 working properly for RYBG on nonparent rows.
=IF(Finish2 = "", "Error", IF([% Complete]2 = 1, "Blue", IF((Finish2  Today2) = 0, "Yellow", IF((Finish2  Today2) = 1, "Yellow", IF((Finish2  Today2) = 2, "Yellow", IF(Today2 > Finish2, "Red", IF((Finish2  Today2) > 0, "Green", "NA")))))))
So now how do you combine Eq1 & 2 so that the sheet (general user adding a row) doesn't need to worry about the formula it will work if it is a parent or child?
Help Article Resources
Categories
Check out the Formula Handbook template!