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

chlod24

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

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))