Create Chart from Dropdown (Multiple Select)

Brandon Bear

Hi and Thanks In Advance,

Essentially I would like to create a chart based on one column of Dropdown (Single Select) data (Status) and one column of Drop Down (Multiple Select) data (Goal). I'm aware I will probably need a helper column somewhere in there, but I cant get ym head around what it might be.

Each row of data is linked to anywhere between 1-5 goals - I would like the chart to show for each goal how many projects are marked with each status - where a project is assigned to more than one goal it should be counted / presented against each goal. For example, a project aligned to goal 2 and 4 with a status of in progress should show in the chart under goal 2 and goal 4 with an inprogress status.

Not sure if this makes sense, hopefully someone can help

Paul Newcome

How do you have your metrics table laid out?

David Fiorino

Don't use multi-select. Set this up as a database where each row is a unique entry of each project with a single-select status and single-select goal.

I don't think you want to use multi-select. Smartsheet does not always count multi-select cells correctly. If someone is adamant about multi-select, I export the data and pivot it outside of smartsheet to fix this and dashboard it in a visualization program.

Paul Newcome

@David Fiorino The multi-selects have never caused me an issue when pulling metrics. As long as you have your base table laid out properly and use the correct functions, multi-selects are rather easily accounted for. The HAS and CONTAINS functions really come in handy for this, and allowing for multi-selects can make it much easier on the users who are entering the data.

klizotte

I have this same question, @Paulnewcome. How should the base table be laid out?

Paul Newcome

It depends on exactly what data you are pulling, how the source data is laid out, and how you want to present the metrics. The basic idea is rows and columns.

If you have multiple teams that you want to count different statuses for (teams A - C and statuses "Open" and "Closed"). Lets assume that the "Team" column is the multi-select since multiple teams could be working on the same project.

Then in cell Open2, I would enter...

=COUNTIFS({Source Sheet Teams Column}, HAS(@cell, Team@row), {Source Sheet Status Column}, Open$1)

Then I can dragfill that one formula down and over to populate the rest of my counts for all teams and both statuses.

Brandon Bear

https://community.smartsheet.com/discussion/comment/285355#Comment_285355

Thanks Paul - after some playing using COUNTIFS and contains, i have managed to set up a metrics table with Status on the first column and each following column having a COUNTIFS for Status and Contains the number of the Status, for example:

=COUNTIFS({Status}, "Awaiting Input", {IHS Goals}, CONTAINS("5", @cell))

Where IHS Goals is the Multi Select.

Paul Newcome

@Brandon Bear Great! Were you also able to get it to work with your chart?

Here's a quick tip for future metrics sheets as well...

Using your latest screenshot as an example, as long as the text in the Status column on your metrics sheet matches the text option in the source data sheet, you can use a cell reference instead of specific text within your formula. That will mean one less portion to update and one more thing you can dragfill. You would only have to write 1 formula for that set of metrics as opposed to 6 formulas.

=COUNTIFS({Status}, "Awaiting Input", {IHS Goals}, CONTAINS("5", @cell))

=COUNTIFS({Status}, Status@row, {IHS Goals}, CONTAINS("5", @cell))

You could also use a helper row across either the bottom or the top where you could put in the numbers 1 - 6 and use a cell reference there in your CONTAINS function. Now we have gone from writing what amounts to 36 different formulas to writing a single formula and dragfilling.

David Fiorino

@Paul Newcome Searching a string using contains is a great approach for this use case with a small finite list of statuses. I learned a lot here!

My issue with the multi-select was trying to create a resource allocation tool by searching the assigned-to field with a large company. Searching a short list of statuses works well, but not with 1000+ names that changed over time given the way SS assigns metadata to multi-select strings.

If I run into a need to use SS for this I will reach out for help! Thanks again.

Paul Newcome

@David Fiorino I'm not sure if this will help you or not, but the delimiter in a multi-select DROPDOWN column is a line break or CHAR(10). The delimiter in a multi-select CONTACT type column is "comma space" or ", ".

Yes. Smartsheet can be a challenge when working with a large number of entries, but if you can keep the number down to less than the maximum number of rows on a sheet then a little creative "out-of-the-box" thinking can go a long way. There are even solutions for having to search multiple sheets. It just depends on your exact needs.

Another thing to keep in mind is that CONTAINS does not work on contact type columns, so you will need to use a FIND function which is case sensitive.

=COUNTIFS({Multi-Select Contact Column}, FIND("John Smith", @cell) > 0)