Formula to check box on header row if any rows nested under it have a specific owner/assigned to

Hello, I am trying to work out if the following scenario is possible.

I have a bunch of parent tasks / child tasks. I have these child tasks feeding into a report/dashboard IF the assigned to is "Client" and want to include the headers where appropriate (ex. I don't want to pull through ALL the header rows on the sheet if there are no child tasks assigned to the client)

I was thinking of making a checkbox column called "include on report" for the header rows, then I can filter the report to include the rows with that column checked. However, I want to automate it with a formula so that if we add a child task and assign it to the Client, it will auto check the box and include that header row. and it needs to EXCLUDE any rows that are assigned to the Client, but the "Done" column checkbox is checked.

Thanks for your help!


  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/28/23

    Hi @chlod24, this should check the header if the Assigned To column contains "Client".

    =IF(COUNT(CHILDREN(AssignedTo@row)>0, IF(COUNTIFS(CHILDREN(AssignedTo@row), "Client")>0, 1, 0),"")

    If you also need to check the box for the children, add this:

    IF(COUNT(CHILDREN(AssignedTo@row)=0, IF(AssignedTo@row = "Client", 1, 0),"")

    You can probably just do this if you want to use the same column to pull everything:

    =IF(COUNT(CHILDREN(AssignedTo@row)>0, IF(COUNTIFS(CHILDREN(AssignedTo@row), "Client")>0, 1,0), IF(AssignedTo@row = "Client", 1, 0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!