Reporting and Dashboarding on Multi-Select Column Type

Anthony Car

Hi, bear with me as I am a pretty new user to Smartsheet (love the tool so far but have hit a snag)

I'm currently utilizing the multi-select column type for a few of the sheets I'm working on. I love the feature, but am having trouble building the exact dashboard I need to report on my multi-select column.

Let's say I have a list of Projects and each Project can have multiple Tasks associated. For those Tasks, there are different Teams that are associated with the Task. Teams are tagged to a Task using the multi-select drop down feature (see example below)

I'd like to build some sort of dashboard that shows me a matrix of Teams and Projects, and how many Tasks are associated with each. Essentially I want something like the screen shot below...

From what I've read, I can create Sheet Summary fields using some variation of the CONTAINS function. I want to avoid that option if at all possible... For the example above that works fine because I only have three possible options for Team in the multi-select field. However, my reality is that there are dozens and dozens of possible teams and I don't want to 1.) create a separate summary field for each team and 2.) have to create a new summary field every time I add a new team.

Can anyone help me get the desired output? My initial thought is creating a separate sheet that parses out each Team on a different line, but i feel like that kind of defeats the purpose of having the multi-select field to begin with.

Any help is greatly appreciated :)

Anthony

Paul Newcome

You are going to want to start by adding a "helper" column on the main sheet that can later be hidden to keep things looking clean. In this column you are going to want to pull the Project into each of the Task rows.

=IF(COUNT(ANCESTORS([Primary Column]@row)) <> 0, PARENT([Primary Column]@row)

Then on the other sheet (assuming it is set up how you have in your screenshot), you would use something that ends up looking like this:

=COUNTIFS({Main Sheet Team Column}, "A", {Main Sheet Helper Column}, Project@row)

Anthony Car

Hi Paul,

So, if I use =COUNTIFS({Main Sheet Team Column}, "A", {Main Sheet Helper Column}, Project@row), this means I'll have to create a separate formula for each individual team. I want to avoid doing that. Is there a summary or dashboard feature that will do a summary of all unique team names without me having to manually tell Smartsheet which one to summarize?

Thanks,

Anthony

Paul Newcome

On the sheet where you are doing the calculations, you can use the first row to enter the team names and then use cell references to that row. Using something similar to the screenshot below would allow you to put the formula in [Team A]2 and then dragfill down and right (note the use of the $ to "lock in" column or row references as appropriate to allow for dragfilling).

=COUNTIFS({Main Sheet Team Column}, [Team A]$1, {Main Sheet Helper Column}, $Project@row)

Anthony Car

Hi Paul,

My apologies, I don't think I'm correctly communicating my issue.

Essentially I want Card View functionality for a multi-select columns. I realize that is not a functionality of Smartsheet. So I'm hoping to build a dashboard (not a Grid as shown above) that can slice multi-select column data anyway I want to see it, on the fly. My initial drawing was mostly to help visualize what I would want the dashboard to look like.

Lets say for example I have 100 teams, and at any given time, these teams could be updated (old teams removed, new teams added, team names changed) I don't want to have to go in a manually validate 100 different teams every time there is a change. I am looking for some functionality that will look at a multi-select column. Return unique names of teams and spit out a sum of how often those teams occur. I'd like to be able to splice this any which way I want.

Again, sorry if I'm not making sense, I'm still new to Smartsheet and am trying to figure out exactly what it can do.