I am working on setting up a dashboard that contains both current month data and running YTD data. We have traveling service people and we currently use a Smartsheet form for them to report their customer time, travel time, office, time, off, time, total time, how many visits they did per month, what their average is next to monthly, quarterly and annual goals, etc. It's a lot of data and I'm looking for some direction from more seasoned Dashboard users.

What is the best way to set up your calculations sheets? I started putting everything on one, but it is quickly getting out of hand. Is it best to have a current month calculations page with all the data points and then save it as a new document and amend the formulas for the YTD running totals? Or, is it best to have calculations pages for each of the data sets and include both current month and YTD fields? My supervisor wants to see key data points for the current month in a dashboard, but also wants to be able to see trends across time. What is your best practice? Thanks in advance!


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    For my dashboards, I have multiple underlying metrics sheets.

    On one metrics sheet I'm calculating daily numbers for our warehouse employees. A set for tasks currently assigned, a set for number closed today, and a set for dollar value closed today. Along with those per employee stats, I capture the total number of new tasks created today, total closed today, total $ value closed today.

    On a second sheet I am pulling those same metrics, but date ranges look for totals for the current month.

    Closed tasks roll off the Active sheet after a few weeks and into a series of archive sheets. These I use to calculate month-to-month trends and totals for some graphs on a management dashboard.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!