Is it possible to filter modified column to look at only certain columns?

Hi, I have a sheet that contains a list of items with 7 columns. I've created a dashboard and on it I'd like to highlight only those rows that have changed. I know there is the modified column but I only want to highlight a row as changed if something was updated in 4 of the 7 columns (e.g. I add a new column and now every row says it's updated but really the information I care about has not). Any recommendations on how to accomplish this? Does Smartsheet allow me to build a query looking at specific columns to see if a modification has occurred within a certain time frame?

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use the Record a Date Automation.

    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

  • Thanks @Paul Newcome . I haven't used that before but will look into it. I just started using Smartsheet so have a lot to learn.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️


    You are going to want to look into automations and the triggers more specifically to see how to set it 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

  • I was able to setup the trigger, then built the report, then added the report to the dashboard showing only rows that had certain columns changed within the last 7 days.

    Do you know a way to highlight WHAT changed along with showing the row? That would allow me to go right to the change vs having to figure out what's new/changed in the dashboard when discussing it with my team.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you tried using this method in addition to the Highlight Changes feature? If you combine the two, the report will only pull rows that were within the past 7 days, and the highlight changes feature should highlight what was changed.

    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

  • Hi Paul, yes I turned on Highlight changes in both the sheet and the report which is working great. I have the "remove formatting" turned off on the report widget on the dashboard. The highlights seen on the report do not carry forward onto the dashboard view.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Are you able to leave the formatting on for the dashboard? I may have a few ideas, but it may get a little tricky and will require some testing if you really do need to leave the formatting off.

    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

  • Yes, I have formatting on. I have a conditional rule to make "Closed" item rows light red. I noticed that carried forward to the dashboard.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    My apologies. I misread your last comment where you said

    "I have the "remove formatting" turned off on the report widget on the dashboard."


    Let me play around with a few ideas and get back to you.

    How frequently do things change?

    Are you open to adding a "helper row" and a few helper columns?

    Are you willing to set up a separate sheet that will receive a copy row automation?


    Again... I make no promises that I actually have an actual solution, but I do have a few ideas that could get rather complex, but would allow conditional formatting to highlight the changed cells which should pull through into the report and then into the widget.

    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

  • The current sheet only has 28 rows of items I'm tracking with only 14 of those not closed. The 14 open rows would likely get weekly updates. The dashboard would be shared once per week highlighting the cells of the rows that changed. If the native highlight changes carried onto the dashboard I'd be done. I appreciate your help. Never used helper rows or columns or copy row automation but willing to learn.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!