Creating a Dashboard with non numerical data

Timmy D.

I have a sheet created from a form that is essentially a Security Activity Report Form. Officers enter their daily duties such as Patrols, suspicious activity, emergency responses, etc.

I want to create a dashboard that will show how many patrols each site is conducting, how many suspicious activity reports, vandalism, etc.

right now I am exporting to Excel, creating a Pivot chart, and so forth. Not difficult but time consuming.

Is there a way to build the dashboard in Smartsheet? I have tried but it will only do it for numerical data, where as Excel will allow a pivot table/chart based on the number of entries in the drop down.

So essentially, is there a way to create and or link my main sheet to another sheet that will calculate how many of each event, in realtime then build the dashboard off of the sheet with the numbers?

If so what formulas do I use?

Paul Newcome

You can use a separate sheet with formulas, or you can create a row report that pulls in all entries, group the report, then use the Summarize feature within the report to get your counts. Then you can use this report to create a chart or graph on your dashboard.

Paul Newcome

You can use a separate sheet with formulas, or you can create a row report that pulls in all entries, group the report, then use the Summarize feature within the report to get your counts. Then you can use this report to create a chart or graph on your dashboard.

Timmy D.

Paul, thank you for your response, you wouldn’t happen to know where I can find how to do that would you?

And would those row reports update live or would have to keep pulling them?

Sorry, I’m not new to Smartsheet just new to pulling data from here rather than exporting.

Paul Newcome

Reports will update live as the source data updates. Here's a link for more info:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

Timmy D.

you’re a lifesaver Paul. Thank you 🙏

one last question because I can’t find a solution. Each one of my columns in my sheet have multiple selections from a drop down. In Excel when I create a pivot table I have to go in and sort the data by attaching another sheet that has all of the associated drop downs listed and I can sort/filter for each of the individual duties. For example: if I want to know how many vandalism activities has occurred at a site I can see that reflected in the chart (see pic) but I cannot for the life of me figure out the same work around for the Smartsheet

in Smartsheet (bottom pic) it just gives me the total count of the column. Not the individual counts of the types in the column.

Paul Newcome

Are you able to provide a screenshot of the source data?

Timmy D.

source data is Smartsheet from a form created to capture these events. But no , I can’t screenshot the sheet due to sensitive information.