Report Total Number of Times a Cell was Modified

Sandra Guzman · 09/03/20

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

David Joyeuse · 09/03/20

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!

Sandra Guzman · 09/03/20

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!

Paul Newcome · 09/03/20

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.

Report Total Number of Times a Cell was Modified

Answers

Help Article Resources

Categories