Formula to check Child Row checkbox if Parent row contains "ABC Dept" (filtering by Department)

ON

I have a "Master" project resource sheet with great grandparent rows for "Current Projects" and "Future Projects", Then grandparent rows for each "Project", parent rows for department and child rows for the individual resources per department. 

I'm trying to create a filter to filter resources by department across all projects. I can manually create a filter that has all resources from a department checked within the filter, but I have to maintain that list! Instead I would like to use a formula in check box column to check automatically if criteria are met and filter based on checked/unchecked status. I will have one column and one filter for each department (5 total) The columns span all projects. I will keep the "include parent rows" option checked in the filter to keep the sheet structure. The check box needs to be ticked if the child row's parent is "department_name" which is in the primary column

I'm struggling to compile the formula to check the box if the parent (department) row is "PM" for example. I don't want there to be any Dedicated cells involved so it can be applied generally to an entire column.

Have been using checkboxes and formula's for a while to generate conditions for conditional formatting and part of me thinks this formula should be simple enough!

Andrée Starå

Hi,

Try something like this.

=IF(COUNTIF(UPPER(Resource@row); FIND(PARENT(Status@row); @cell) > 0); 1)

The same version but with the below changes for your and others convenience.

=IF(COUNTIF(UPPER(Resource@row), FIND(PARENT(Status@row), @cell) > 0), 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Andrée Starå

I made a mistake in the column name in the first one so try this one instead.

Try something like this.

=IF(COUNTIF(UPPER(Resource@row); FIND(PARENT(Project@row); @cell) > 0); 1)

The same version but with the below changes for your and others convenience.

=IF(COUNTIF(UPPER(Resource@row), FIND(PARENT(Project@row), @cell) > 0), 1)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

ON

Hi Andrée Starå

Thank you so much for your reply! The formula you posted gets me half way!! Somehow/Somewhere I (think) need to add a separate criteria to match the "parent" row's name eg "PM" or "DEV" and only check the box if the parent row matches name matches. Your formula very neatly checks all child rows (and not the parent rows which is partly what I need) but doesn't distinguish between the different parent rows (my departments) PM's or DEV's etc.

=IF(COUNT(UPPER(Resource@row), FIND(PARENT(Project@row), @cell) >0), 1)

Q: Where do I specify the search criteria: "PM" in the formula (everything I've tried comes back "unparseable"

Q: Should I be searching/Specifying the entire "Project" column to search? When I try and add the range [Project]2:[Project]826 I throws an error...  

Q: I've never used "@cell" before should I be changing that out for a specific cell or text? If I swap that for the "PM" text it makes no difference to the result of the formula.

The Idea is to have a column for all "Project Managers" to be checked regardless of project - just checks them if they have the role of PM. The projects are broken into Departments (the Parent rows "PM" "ARC" "DEV" Etc) I will have a column for each department.

ON

Hi Andrée

Firstly, Thank you so much for your reply! I did reply this morning with screen shots but it said it needed to be reviewed and has not shown up yet. :-( Hopefully with no screenshots it will be quicker.

The formula you provided didn't quite give me the result I was looking for... it checked every "child" row brilliantly (much nicer than the option I have in another sheet). However I'm trying to check only the boxes under a parent row if the parent row is a particular dept "PM", "DEV" etc so in my mind I need to add the text criteria into the formula. I will then have a checkbox column for each department.

If the "Project" Column has a "PM" parent row then I want to check the child rows of that parent only.

I've had several failed attempts including LOOKUP formula to identify "PM" in the column and then COUNTIF to check the box but haven't been able to work it out.

ON

the screen shot from earlier on it's own

ON

So... I cheated...

I have a master list of users that feeds the sheet I was trying to put the check box column in to build the filters. 

In the master user list I created a formula for each department parent/child section with a fixed ref to the parent row "project" column (one for each):

=IF($Team$8 = "PM", "Project Manager", "")

When you insert a new row it auto populates all the formula(s) in the new row.

In the sheet listed above I created a column called "Job title" and used the following formula:

=IFERROR(LOOKUP(Resource1, {2019 Team Days Range Job Title Link}, 3, 0), "")

It matches the "user name" (Resource) to the column in the master list and then populates the Job title. I can now filter based on Job Title and Location.

I would still love to get to the bottom the original question if anyone else has any input.

Andrée Starå

Happy to help!

I'll look again and get back to you!

Andrée Starå

Haha!

Nice work! 

Can you explain again how you would like the original formula to work?

Don't you want it to check the box as long as the parent is the same as the Resource?

ON

Hi Andrée

Thanks again for your help! I've added a screen shot with the columns labelled accordingly and annotated. Hopefully the image makes sense even if my description doesn't :-D

Kind Regards

Olly

Andrée Starå

I'm always happy to help!

The way I understand your need my original formula should work, I think. Maybe I'm misunderstanding.



ON

Hi Andree,

Your formula doesn't match the row title "PM" with column title "PM" as a criteria for checking the box. It checks all boxes of child rows under any parent row.

I need to distinguish by department row within all projects. The department column is some 825 rows deep with multiple projects ("grand parent" rows - for example the dark grey row with project title "Rats and Mice", each project row has descendants for "PM", "DEV", "ARC", "QA" etc and those have their own descendants for resource names (the people assigned with relevant job titles). The formula needs to be universal to check department child rows (the resource names) in the relevant department column but not other department rows. It needs to be universal from cell 1-825 in each department column without the need to manage the location of the formula. I will be adding projects and relying on Smartsheets auto population to insert the formulas into new rows.

Structure equals:

- Project Row (Project name) (Lives in Column called "Project") (Grand Parent row)

   - Department Row (PM, DEV, BA etc) (Also lives in "Project" Column) (Parent row)

      - Resource Name ("John Smith" etc) (Lives in Column called "Resource") (Child row)

So I need the formula to do the following:

If child row (Resource Name) has parent (Department Row) that = "PM", check box in PM column.

Your formula does the following:

If row has parent, check box (missing the search criteria for the department name in the department row) Does that make sense?

I would change the "PM" criteria in the formula to "DEV" and populate the DEV column etc. Totalling 5 department columns to match the 5 department rows in each project. Because the list is one massive list of nested Project rows I want to be able to filter based on department.

Don't worry too much at this point as I say, I cheated :-D Thank you so much for your help and replies!! :-) If this is bugging you as much as it is me, please feel free to have another go... Maybe I can create a copy of the sheet and share it with you. Let me know.

 

Andrée Starå

You're welcome!

Feel free to share it with me because it's bugging me  (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. Share to: andree@getdone.se

Best,

Andrée

ON

Hi Andree,

I've created a shared workspace and shared it with you. If you wanted to edit the comment above remove your email from.

Krgds

Olly

Andrée Starå

Thanks!

I'll take a look and get back to you!