Red Flag If Statement and Children function

Options

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?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!