How if a box is checked it any point in the process it will mark it checked for that section

2»

Answers

  • Jett
    Jett ✭✭✭

    Hi @Kelly Moore

    The sheet is working. However we added a column in "day before due date" and it will send an alert the day before a due date if something is not marked complete or N/A. We have the same columns as previous screenshots. How do I have the parent highlight yellow if the day before is today and not marked complete other N/A?

  • Kelly Moore
    Kelly Moore Community Champion

    hey @Jett

    If I understand, are wanting any Parent to indicate a due date - 1

    You will need a helper column to find your criteria then use conditional formatting to show the yellow highlight.

    Helper column (checkbox)

    =IF(AND(COUNT(CHILDREN([Primary Column]@row))>0, TODAY()=[Day before due date]@row),1)


    create a conditional formatting rule when this helper column is checked


    Will this work for you?

    Kelly

  • Jett
    Jett ✭✭✭

    Hey @Kelly Moore

    I have a question I would like the parent level to highlight yellow when the day before due date is today. I have a formula but it seems to only be returning a 1 on the child level. I need the one on the parent levels. If it was on the main parent that would be great so when all rows are collapsed it will be yellow indicating something is due soon for that item.

  • Kelly Moore
    Kelly Moore Community Champion

    Hey @Jett

    Are you using the formula I suggested above? This should flag any Parent, regardless of their level.

    Kelly

  • Jett
    Jett ✭✭✭

    @Kelly Moore

    Yes and it is not returning any values. : (

  • Kelly Moore
    Kelly Moore Community Champion
    edited 01/29/25

    Hey @Jett

    Sorry, I didn't realize the Parent row didn't have the relevant date in the Day before Due Date column. This should do it for you. It should check the top level Parent as well as any Parent below it

    =IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell) > 0), 1)

    And if you don't already have the Complete vs incomplete criteria sorted in another column, your formula will be:

    =IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell, DESCENDANTS(Status@row), AND(@cell<>"Complete", @cell<>"N/A")) > 0), 1)

    Will this work for you?
    Kelly

  • Jett
    Jett ✭✭✭

    @Kelly Moore Hi,

    This is working however when a status is marked complete or N/A then I need it to make the box unchecked. So if it is complete or N/A there is no need to worry about the task.

  • Kelly Moore
    Kelly Moore Community Champion

    @Jett

    Using my formula above in my test sheet, I have check marks for the Parent and Grandparent rows:

    • IF the row is a Parent row
    • AND any Child is Today's date
    • AND the Status of any Child is not Complete or NA

    Are you wanting your Child rows to be checked as well? I must have misunderstood your original request.

    Try this if you also wish Child rows to be checked:

    =IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell, DESCENDANTS(Status@row), AND(@ )) > 0), 1, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = 0, [Day before Due date]@row = TODAY(), Status@row <> "Complete", Status@row <> "N/A"), 1))

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!