Track changes in a data element over time

Matt.Robinson
edited 12/09/19 in Smartsheet Basics

A customer of mine would like to be able to see what rows in a project plan have changed their task status over a given period - probably a week. 

A scenario would be in preparation for a weekly team update meeting, the customer would like to be able to see a summary of what has changed from 'Red' to 'Yellow' or 'Green' and vice versa since the previously weekly meeting. 

My first notion for a minimum viable solution is to take a snapshot of the plan once a week at a pre-determined time, and then compare the previous version to the current state version, however this might get unwieldy and involve a lot of copying and pasting of a plan with 200+ rows in it, many of which change, get added to, are deleted, etc over time. 

Has anyone found an elegant way to solve for tracking changes to cells over time, or is there a piece of SmartSheet functionality I'm not aware of that can help implement this? 

Tags:

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Matt,

    I can think of a few different ways to accomplish this, but the right solution depends on your specific use case. Can you share some screenshots or the sheet(s)?

    I hope this helps you!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Matt.Robinson
    edited 10/19/18

    Hi Andrée, thanks so much for helping take this on. I've attached a picture. 

    The minimum viable product would be to show which rows changed task status and how many rows changed in task stage since the previous weekly meeting. So hypotheticaly if the meeting is on Wednesday morning 10/17, we'd be able to automate a report that shows what has changed on 10/24 since 10/17. 

    Even better would be able to track this change over time / multiple weeks and show trend lines. 

     

    SmartSheet help.PNG

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    One way to do it could be to have columns for each week and after each meeting, the previous weeks status is copied to the next week. The report is updated to show relevant data. This information could then be shown in another report that could show the changes for each task on a chart in a dashboard.

    Another way to do it could be to have a report and enable highlight changes for the last seven days during the meeting, and this could also be automatically sent each week.

    I have other ideas brewing, and I will get back to you if they're better than the above options.

    Would that work for you?

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Another way to do it could be to have a report and enable highlight changes for the last seven days during the meeting, and this could also be automatically sent each week

    ...is there a way to display the highlight changes in a report ON THE DASHBOARD itself?

    The repost shows yellow highlights, but widget on Dashboard of same report looses the highlight.

    Thanks,

    Doug

  • Sldollman
    Sldollman ✭✭✭

    @Doug Tambling Did you find a way to display the report highlights on the Dashboard? I'm having the same issue.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you create a new sheet and add in a Created (date) column, you can use the Copy Row automation to copy a row over when the change is made. This will timestamp the change, and a report can be pulled to show applicable rows from this second sheet.