I am coming up with formula errors when trying to make the parent flag red if any children are red

Deane Nash

The CHILDREN have, for example, =IF(AND([End Date]323<TODAY(),[% Complete]323<>"1"),1,0) and that works to turn flag red in all children but tried all kinds of COUNTIF formulas to also turn the parent red if any one or more of the CHILDREN are flagged red but all attempts are failing. Any ideas?

Paul Newcome

Try this...

=IF(COUNTIFS(CHILDREN(), 1) > 0, 1)

Deane Nash

Thanks Paul, still doesn't work. Here is what happens when I make a child past due

Deane Nash

I also tried ANCESTORS to ensure even 2 layers down were flagged but it didn't work.

Paul Newcome

This formula would go in both row 322 AND 321.

Deane Nash

I tried that but it didn’t work, unless I typed something incorrect, will try again. I do want row 321 to flag red if any ancestors are red as one of my reports needs to list 321 then any late task listed itself but don’t need all the sub parents, if that makes sense. I need the report to show only the top task (321) then individual late tasks only so when you read the late report, you know which projects from master timeline have late tasks.

Deane Nash

Hi Paul, thank you, it worked once I put the CHILDREN into both lines, 321 and 322. Now, can I get it so if any individual tasks are late but the immediate parent is not late, the immediate parent stays off but the overall parent goes red? If lines 323, 324, or 325 go red, then just line 321 goes red as well, not 322. I have an At Risk report and need line 321 to be flagged as well as any actual tasks so you can scan down the report and know which task is for which sub-project (line 321).