Flag Descendants

Options

I am looking for help with a formula that will "flag" the row if it is past the end date and not 100% complete (I have this part figured out), but I am also looking to flag the row if any of the descendants of the row are flagged. IE if the child is flagged, all parent rows should be flagged.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    Say your flag column is called "Flagged":

    =IF(COUNTIF(DESCENDANTS(Flagged@row), =1) > 0, 1, 0)

    English: Count flagged rows that are descendants of this row's "Flagged" cell, if the count is greater than 0, flag this cell, otherwise, don't flag the cell.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • brodyleonvantage
    Options

    Thanks Jeff but I am stuck on putting both formulas together. Currently what I have is below but it is not working as I intended.


    =IF(AND([% Complete]@row <> 1, [Target End Date]@row < TODAY()), IF(Hierarchy@row = 0, IF(COUNTIFS(CHILDREN([Task Late]@row), @cell = 1) > 0, 1)))


    Do you see what I am doing wrong here?

  • brodyleonvantage
    Options

    =IF(AND([% Complete]@row <> 1, [Target End Date]@row < TODAY()), IF(COUNTIF(DESCENDANTS([Task Late]@row), =1) > 0, 1, 0))


    Sorry this is the formula I currently have ^^^

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    I think you're just missing an end parentheses to close off the AND statement. Right there:

    And you may have an extra one at the very end. 👍️

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • brodyleonvantage
    Options

    Jeff - I just figured out the correct formula. It was very similar to what you posted. I was still getting a syntax error with the formula you posted.


    Here is the final version I am using:


    =IF(AND([% Complete]@row <> 1, [Target End Date]@row < TODAY(), NOT(ISBLANK([Target Start Date]@row)), NOT(ISBLANK([Target End Date]@row))), 1, IF(COUNTIF(DESCENDANTS(Late@row), =1) > 0, 1, 0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!