Flag Descendants

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
-
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!
-
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?
-
=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 ^^^
-
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!
-
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
Categories
Check out the Formula Handbook template!