Reporting and Dashboarding on Multi-Select Column Type
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 :)
Help Article Resources
Check out the Formula Handbook template!