Need to apply conditional formatting to a parent row when a child row date is in the past

In this particular instance, I want the "Production" row to display in Red/Bold since it has subtasks that are past due and not ticked "completed". Is there a way to do this easily?


Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Justin,

    You would need to add a so-called helper column with a formula that, for example, could check a box for the parent row that meets the conditions and use that in the conditional formatting rule.

    Make sense?

    Would that work?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks Andree, would you be able to tell me more specifically what kind of formula I need to enter into the column? I get that it's probably an =IF function, but I'm not sure how to define the criteria to flag if the child is red then the parent should be as well.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Justin Birchard

    Try something like this. Add the formula to the parent in the flag column.

    =IF(COUNTIFS(CHILDREN(Finish@row); <TODAY(); CHILDREN(Completed@row); 0); 1)

    The same version but with the below changes for convenience.

    =IF(COUNTIFS(CHILDREN(Finish@row), <TODAY(), CHILDREN(Completed@row), 0), 1)

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!