Dashboard Help

I want my dashboard to track a couple of things. First the total number of yes and no's in this sheet (form dumps into it). Then I want it to track the "What info is missing" part and a count for how many for each monthly.

I want it to look something like this but with each individual question in "What info is missing" to be its own column if that makes sense.

Here is the formula sheet I have for this dashboard…

Would this be a good way to do this? I have a lot of questions that will be tracked.

Answers

  • SSFeatures
    SSFeatures ✭✭✭

    Hi Ty,

    A good way to do this would be to create two reports to format the data how you want, and then use the Report Widget in your dashboard.

    To count the total number of Yes and No's in the sheet, do the following:

    1. Create a report.
    2. Add the sheet to the report.
    3. Select the Yes/No column.
    4. Click "Group" then "Group By" the Yes/No column.
    5. Click "Summarize" then "Count".

    Then create a report widget in the dashboard to access this report.

    To create a report for all of the "what info is missing?" responses, do the following:

    1. Create a report.
    2. Add the sheet to the report.
    3. Select the column.
    4. Click "Filter" then "What info is missing?" and set it to "is not blank" to filter out any empty rows.

    Then create a report widget in the dashboard for this report as well.

    The dashboard will look something like this:

    Does this look like what you want?

    Best,

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Automatic sorting, sorting with filters, saving sort settings
    • Spell checking
    • Report PDF generator that supports grouped and summarized reports

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is the [What info is missing?] column a multi-select dropdown column or single select? How exactly would you want your counts for the Yes/No displayed on the dashboard?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Yes, it is a multi select dropdown column. The whole process is an audit form. So people filling out the form have the option to select multiple things in the "What is missing" column when filling out the form. I will put the yes and no's in just a percentage complete pie chart probably. Or just a sum of yes vs no's using a formula cross referencing sheets.

    I am most concerned about seeing monthly misses divided up by those categories. (Ideally we want to track them going down each month). This is why I want to use a graph and not a report if possible.

  • @SSFeatures This could be possible I think. One concern is I want it to display divided up by months. It is an audit that I made basically so showing graphically would probably just be more pleasing I would say to people who will be viewing it. Can we divide reports up monthly?

  • @SSFeatures @Paul Newcome

    Here is a better explanation of what I am trying to do. I want all the options I have in that multi dropdown column to be across on this metric sheet, like pre-surgical drink for example. I have the months in the rows because I want to know the number of times each item across (like pre-surgical drink consumed) was missed each month. I will include my formula below. for some reason it isn't working. Is it because I have multiple things in the "what is missing column"?

    Why doesn't the regular sheet have the little arrows in the cells indicating that the column is being pulled into metric sheet? Did I fill out the formula wrong?

  • @SSFeatures @Paul Newcome

    It seems like I can't use this approach unless you guys know what I am doing wrong? For this test I selected every option in the drop down menu but only the first thing in the list is populating on my metric sheet. Any work around for this or a different formula? Maybe not even possible?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For the yes's and no's, my suggestion is a basic report grouped by the yes/no column then use the summary feature in the report to get the counts. From there you can use that report to create your percentage pie chart.

    For the COUNTIFS, you will not not be able to use a report because you cannot group on a multi-select column. You will want to incorporate a HAS function into the COUNTIFS along the lines of

    =COUNTIFS({Multi-Select Column}, HAS(@cell, "Text to search for), …………………………….)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome

    Still not sure what is going on. Think I have the formula wrong still. Do I need an AND in here some where to make each cell count for both date and option in drop down? It pulls a "2" in for this formula but it should just be a "1".

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    AND is "built in" to the COUNTIFS function. Try applying a filter to the source sheet that replicates the range/criteria sets and see what comes up.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome

    Not sure what you mean. How would applying a filter be able to pull the individual counts of things sorted by month and the "What info is missing" column automatically into the metric sheet?

  • @Paul Newcome

    How about something like this?

    Could I do this but instead of the "No" make it months?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The filter in the sheet will show which rows are being pulled in by the formula. It is to double check everything to see if there is a back-end issue, and issue with the formula, or potentially a missed row when you were scanning the data visually determining what the count should be.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Could a possible problem be that I am not including the "yes" or "no" within my formuals? right now it is just the date and the info missing. Would we need to add this in for some reason? I had no success finding a problem when using the filters.

  • @Paul Newcome I also found this link that talks about a helper column counting the number of entrees within a multi select column. Not sure if this would allow this to work? Or if it simply just returns the number of things that are selected in the column (not the item itself).