RYG Ball Formula

Josh Canady

Can you change formulas to make "Not Started" be the same as if it was blank? If I deleted the Not Started lines then my parent row would match my child/ children rows. (See snip # 2). I'm not sure if it's possible since you can't paste "Not Started" into a cell; it just changes to "Not Started".

My current formula:

=IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "",

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

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

IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Yellow",

IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "---")))))

Paul Newcome

Does this work?

=IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "", IF(COUNTIF(CHILDREN(), OR(@cell = "Green", @cell = "Not Started")) = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", "---")))))

Josh Canady

It does, but I still need the parent row to be yellow if a cell has "Not Started" and it is not crossed out. This change makes it green either way.

Paul Newcome

I didn't realize you were specifically looking for crossed out rows. My apologies. How are you crossing them out currently?

Josh Canady

If certain rows are not need, I just select those rows and hit the strikethrough button on the tool bar. I'm just looking for a solution that would automatically allow the parent row to update if rows were crossed out. Deleting the "Not Started" cells works, it's just an extra step.

Paul Newcome

Using the formatting like that cannot be picked up in a formula.

Josh Canady

OK, that's what I thought. Thanks for trying!