INDEX/MATCH with checkboxes and contacts

Travis23997

Hello, I am working on a sheet that I created that will act like a team member matrix. In the sheet I have two columns that list the name of the project and another column listing the names of who is apart of the project. The other columns in the sheet are all identifiers like (project owner, sponsor, leader, etc.) and are checkboxes. There is a Masterfile that lists all the projects with all the info needed including who the owner, sponsor, leader, etc. is. The idea I have is when the names are populated in the member matrix I want the corresponding column to check itself if that name matches with the Masterfile. So for example if my name is one of the team members and I am the project owner (listed in the Masterfile under a column labeled "project owner") then the checkbox under project owner will be marked.

I believe it is possible with a vlookup or INDEX/MATCH formulas that I could use for each of the checkbox columns in the member matrix sheet. I am just hitting some bumps in the road of how to accomplish this as I am fairly new to using these kind of formulas. Any help would be greatly appreciated.

Paul Newcome

Ok. In that case I am going to suggest an IF/COUNTIFS combo like so:

=IF(COUNTIFS({Master Sheet Project Lead Column}, @cell = [Team Member List]@row)> 0, 1)

Then you would create a new cross sheet reference based on role and update the formula accordingly to check the other boxes.

Paul Newcome

Are you able to provide screenshots (with sample data if needed) for reference?

Travis23997

Yeah np, I have two different screenshots. One with the columns I am looking to get checked off and the other is the master file checking who is under what identifier(owner, coach, sponsor, etc.)

Smartsheet member matrix.png

Smartsheet Master File.png

MichaelTCA

Hello @Travis23997

I think this is the route your heading, correct me if I'm off track.

A checkbox is a boolean variable so the function would result in a true/false or 1/0.

The COUNTIF() function will allow you to count a cell based on a criteria, such as whether a cell is blank or not (project owner assigned or not). It really wouldn't matter if the results are more than 1.

=IF(COUNTIF(RANGE,"cell is filled with project owner")>0,1,0)

1 means it's checked and 0 means unchecked.

Index/Match will bring the values that are contained within those cells to the location and you will get an error if you are trying to enter a contact into a checkbox.

Paul Newcome

I'm still not sure I follow. Are you able to show some sample data that doesn't have to be hidden so that I can see what you are wanting and where?

Travis23997

What I am trying to accomplish is there will be a list of names in the team member matrix that fall under the "team member" column. In the master file there are columns labeled as project coach, owner, sponsor. If a name falls under Project Lead in the masterfile (like mine is in the photo) I want it to check the box that follows the row of the name of the person to the column that identifies what they are in the project.

Again let me know if this makes sense, there is not a ton I can do to show you some of the data without blocking some of it off. Also I am looking to put the formulas into the cell with the checkbox.

Team Member Matrix.png

Master File New.png

Paul Newcome

Ok. In that case I am going to suggest an IF/COUNTIFS combo like so:

=IF(COUNTIFS({Master Sheet Project Lead Column}, @cell = [Team Member List]@row)> 0, 1)

Then you would create a new cross sheet reference based on role and update the formula accordingly to check the other boxes.

Travis23997

Hello @Paul Newcome, it has been a few months since working on other smartsheets builds. I just recently ran into an issue from the original ask of this question. The formula works fine if the contacts from the masterfile only lists their contacts once. Although, if that name appears multiple time, for instance if my name was listed as project sponsor, process owner, and project lead for different project rows the formula will check all the boxes in the Team Member matrix sheet. Not sure how I can only make the reference look at a given row and find the contacts under the appropriate identifier columns (process owner, etc.)

Paul Newcome

You can include another range/criteria set in the COUNTIFS to filter out rows for a specific project.

Travis23997

So with the current formula I am using:=IF(COUNTIFS({General Project Tracker - Sponsor}, @cell = [Team Members List]@row) > 0, 1). Would I need to use another cell reference to select the project name row from General Project Tracker? Not too sure how to add the other criteria set in the formula.

Paul Newcome

It would be another range criteria set.

{Source Sheet Project ID Column}, @cell = [Project ID]@row