Report Total Number of Times a Cell was Modified

Sandra Guzman
Sandra Guzman ✭✭✭✭✭✭
in Formulas and Functions

Hi Smartsheet Community!


I need to be able to report on the total number of times one specific date field has been revised over the course of the project life cycle for trending purposes. I like that we have the ability to view the cell history by right clicking on the cell itself, but it would be nice if there was a way to easily report on the cell (or sheet) history activity without over complicating the process.


In my search for suggestions I did find a majority of replies that suggested using API or Zapier. I do not have the option to use either of these at the moment, so I am really looking for the least complicated workaround solution.


I found a suggestion posted by @Paul Newcome in the post copied below. I think I am going to try and see if I can do something similar with simply copying the modified date column to another sheet.

https://community.smartsheet.com/discussion/34131/track-changes-in-a-data-element-over-time


I also found the following suggestion posted by @Andrée Starå. A little more involved with formulas but I thought this was a good suggestion I'll keep in my back pocket.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier


Are there any other suggestions for creating a formula that would simply count the number of times one specific cell has been modified without having to create another sheet? I am all about the helper columns and think I am on my path to following Paul's lead of having alot of hidden columns for my reporting needs.


Thanks in advance!

Sandra

· Share on FacebookShare on Twitter

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @Sandra Guzman

    Sadly there's no way to make a counter in smartsheet without having another sheet. Even helper column won't help to make a counter, it'll end up in circular reference. Both Andree's and Paul's solutions are really useful on many levels, but they need some tweeking if you want to only count the rows :) (in this case, auto created column won't help at all, thought it may be useful on various occasions).

    A copy row automated workflow, based on the cell you want to track changes (or the whole row?) is exactly what you need for this workaround. Then use a COUNTIF or COUNTIFS function, depending on what you have to differentiate the row in the other sheet should do the trick.

    Something like:

    =COUNTIF({Main Column Range on the second sheet}, [Main Column]@row} if you have a unique column to easily recognize the row.

    or

    =COUNTIFS({Column 1 Range}, [Column 1]@row, {Column 2 Range}, [Column 2]@row) if you need more than one column to identify the row.

    Hope it helped!

    · Share on FacebookShare on Twitter
  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭

    Thanks @David Joyeuse for providing such a quick response!


    I will give your formulas a test and see if this satisfies my needs. I do have a WBS column for all our tasks entered into the project plan so I am thinking that this would satisfy differentiating the rows.


    I'll follow up and share my results on this thread. Thanks again!

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I agree. A basic Copy Row automation set to trigger based on your particular column you want to track and then a COUNTIFS with cross sheet references looking at how many times that particular WBS is in the copy sheet should work.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!