Red Flag If Statement and Children function

Ischegk

Hi All,

first of all, I am new here, so thanks for every help in advance. I did do some research in the forum and tried to figure it out myself, even trying to adapt similar codes to mine, but just couldn't figure out a solution that works.

I have a "Task at Risk" Column that shows a red flag when the task will be do the next 2 days or earlier/overdue and is not set to 100%. Now I also want to the Parent flag to turn red if a red flag of it's child turns to red, so I can refer the mother row (1st row) to the another sheet.

Code i have so far:

=IF(AND([End Date]@row < TODAY(2), [% Complete]@row <> 1, NOT([Schedule Health]@row = "Blue"), NOT(ISBLANK([End Date]@row))), 1, 0)

I guess something like IF(COUNTIF(CHILDREN() =1), 1,0) needs to be integrated into my code, but I can just not put it in right so it will work.

Anyone of you got a solution for me?

Paul Newcome

So you have both of your separate IF statements already figured out. That's the trickiest part really. Now you just need an IF statement that says if it is a child row, run the child row formula, otherwise run the parent row formula.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, child_row_formula, parent_row_formula)

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([End Date]@row < TODAY(2), [% Complete]@row <> 1, NOT([Schedule Health]@row = "Blue"), NOT(ISBLANK([End Date]@row))), 1, 0), IF(COUNTIF(CHILDREN(), 1)> 0, 1,0))

Having said that... I think we can simplify this a little bit.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(2), [% Complete]@row < 1), 1), IF(COUNTIFS(CHILDREN(), 1) > 0, 1))

Paul Newcome

So you have both of your separate IF statements already figured out. That's the trickiest part really. Now you just need an IF statement that says if it is a child row, run the child row formula, otherwise run the parent row formula.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, child_row_formula, parent_row_formula)

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([End Date]@row < TODAY(2), [% Complete]@row <> 1, NOT([Schedule Health]@row = "Blue"), NOT(ISBLANK([End Date]@row))), 1, 0), IF(COUNTIF(CHILDREN(), 1)> 0, 1,0))

Having said that... I think we can simplify this a little bit.

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND(ISDATE([End Date]@row), [End Date]@row < TODAY(2), [% Complete]@row < 1), 1), IF(COUNTIFS(CHILDREN(), 1) > 0, 1))

Ischegk

Thank you for your quick answer. Definitely helped me and worked out.

Cheers!

Paul Newcome

Happy to help. 👍️