Other than Sheet Activity Log and cell activity, can I tell what something has been changed from?

BethWork

I have a Smartsheet where there are dates I have put in and I want to see what it's changed from after I've made a change. I know I can look at the sheet activity or look at individual cell activity, however, I want to see those dates side by side. I need to be able to show what I have scheduled for something and then what the new schedule is in my sheet.

I know I can export the sheet activity log, but that's not really an easy method because it tracks every single change that I made to the sheet. Doing that means a lot of manual work on my part.

有一个公式我可以创建吗?我not sure there's a way around it, but I'm hoping someone has come up with something creative.

Cory Page

Not sure if this would work but I have a few dashboards that I like to monitor changes on as well. In my case I schedule a full backup of the data using automation's "Copy" and then I time stamp everything at the same time. Then I use Vlookup or index/match to bring in the previous values back into my main sheet. I dont know the data you're using and I am sure it would require some thinking to make sure you're pulling in the previous data correctly but its doable for sure. Whenever new rows are added to the main sheet simply copy them over to the historical sheet using automation. You could clean up the index even better by using an if statement with your index/match so that you only return a value if both the historical sheet and the main raw sheet are not equal.

This could get messy if one your data is changing frequently and I would set up some kind of archive for the historical data. I might set up a helper column that counts for duplication so that anytime you have more than 1 of the same rows in your historical data it automatically moves the oldest one to an archive sheet. I like using the Smartsheet column property "Auto number", this will give you a unique value to tie everything together once you start having multiple copies of the same row.

似乎很多工作但其可行的和only way I know of that could quickly track historical changes using formulas and in real time.

BethWork

@Cory PageYes, that's exactly what I'm talking about. I should have thought of the copy automation. I use it for other purposes where I want to track the data from my main sheet. However, I don't typically then vlookup or index/match it back up. I will do some playing around with that idea to see if it gets me where I need to.

I really am just trying to track when a date changes. For instance, if I have 50 installations scheduled this week, but 5 of them get rescheduled or cancelled. I want to be able to say, I had 50 scheduled, 5 cancelled/rescheduled and 45 installations completed for this week. Where my gap is, at the end of the previous week I know the total number of installations I have scheduled, but as my week progresses and my dates change, I'm only able to tell what was completed, but not the original total scheduled. My intent is to get a better idea of my completion percentage. Maybe there's an easier way to get that, but I don't know it.

Paul Newcome

Depending on how frequently things change and how many rows you are tracking, have a look at this method that I developed for tracking historical data. Basically you use a copy row automation set up to trigger based on whatever you need, then you use INDEX/MATCH or INDEX/COLLECT to pull from the copy sheet back to your source sheet.

The thread below is talking about grabbing a date/time stamp, but you can use this method to grab any kind of historical data.

While trying to figure something else out, I stumbled across a new option for having specific actions date\/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.<\/p>\n

.<\/p>\n

DISCLAIMER:<\/strong><\/u><\/p>\n

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.<\/strong><\/p>\n

My personal use for this solution is tracking a one-time change on each row (think \"Completed\" box gets checked).<\/strong><\/p>\n

.<\/p>\n

Here's how it works...<\/p>\n

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.<\/p>\n

Set up the automation to trigger according to what you want to capture the date\/time stamp for.<\/p>\n

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.<\/p>\n

This effectively captures the date\/time stamp for a specific action.<\/p>\n

.<\/p>\n

Yes. The date\/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.<\/p>\n

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date\/time stamp natively within SS.<\/p>","bodyRaw":"

While trying to figure something else out, I stumbled across a new option for having specific actions date\/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.<\/p>\n\n

.<\/p>\n\n

DISCLAIMER:<\/strong><\/u><\/p>\n\n

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.<\/strong><\/p>\n\n

My personal use for this solution is tracking a one-time change on each row (think \"Completed\" box gets checked).<\/strong><\/p>\n\n

.<\/p>\n\n

Here's how it works...<\/p>\n\n

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.<\/p>\n\n

Set up the automation to trigger according to what you want to capture the date\/time stamp for.<\/p>\n\n

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.<\/p>\n\n

This effectively captures the date\/time stamp for a specific action.<\/p>\n\n

.<\/p>\n\n

Yes. The date\/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.<\/p>\n\n

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date\/time stamp natively within SS.<\/p>","format":"html","dateInserted":"2019-11-21T08:29:48+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/63561\/date-time-stamp-option","embedType":"quote","name":"Date\/Time Stamp Option"}"> https://community.smartsheet.com/discussion/63561/date-time-stamp-option

Cory Page

Humm, I am pretty sure the copy feature would work but you may not actually need to create yourself a backup since it seems you just need to track a few flags. I would try creating a couple of new columns first rather than moving everything over for a full backup. Below my original Scheduled date will always stay the same but in my helper column I can now see the rescheduled date. Sorry its a bit long hopefully its helpful.

It sounds like you already have a column for your filter flags scheduled, rescheduled, canceled all you need is a column to indicate the Rescheduled date. You could also indicate the cancelled date in this column as well if you want.

Then you can use the below formulas to calculate each item. I default to countifs but you can use countif as well in this case since we only have 1 criteria.

=COUNTIFS([Apt date 2]2:[Apt date 2]17, "Scheduled")

=COUNTIFS([Apt date 2]3:[Apt date 2]18, "Cancelled")

=COUNTIFS([Apt date 2]4:[Apt date 2]19, "Rescheduled")

My guess is you want to be able to track over a set number of days though so you may need a helper column to track the number of days since a given date, as you mentioned past week.

If this is needed I created the first column

=COUNTIFS(SUM(TODAY() - [APT date]@row), <=7, SUM(TODAY() - [APT date]@row), >0)

然后我更新我的前面的计算示例: Now this will count the numbers of each item only if it was flagged as within the past 7 days in this example. You can always change the above helper column to match whatever date range you want or use a different formula for workdays to tighten you % time frame.

=COUNTIFS([Apt date 2]2:[Apt date 2]17, "Scheduled", [Helper column (within the past 7 days)]2:[Helper column (within the past 7 days)]17, 1)

Finally I also suspect you will want your rescheduled date as well as your original date in the same column. If thats the case then create one last column and drop a nice if statement in there. A bit of clean up would be needed as I just rushed through the examples but I think this would be a bit easier than dealing with the automatons and more sheets. It will also work well with reports just in case you want to set up automatic exports and things.

=IF([Rescheduled date]@row <> "", [Rescheduled date]@row, [Original APT date]@row)

:)

Paul Newcome

@Cory PageIf you are not copying data, then how are you capturing the historical data?

Cory Page

@Paul NewcomeBased on the last response it seems like Beth simply needs to track the total # of rescheduled, cancelled and scheduled without loosing the original schedule date. In this case a historical log or secondary sheet may not be necessary as we could simply add a new column to track the rescheduled date. In the above examples one could use a formula to monitor all 3 of the items based on a given date range in my example the last 7 days. It doesn't use any automation but it could be a simple solution to track and count the number of items within a set date range. In a nutshell its keeping the original data rather than overwriting it using a helper column and an if statement to pull everything back into one row. It may not work for Beths needs but if it does its way easier than managing a secondary archive/audit sheet, if not then we would need to create some automations I guess.

Paul Newcome

@Cory PageI must have been typing a the same time as@BethWorkbecause I didn't see that comment.

@BethWorkAre you able to clarify? Your first post makes it seem like you are wanting to pull in the historical data to display next to the new data.

"I have a Smartsheet where there are dates I have put in and I want to see what it's changed from after I've made a change. I know I can look at the sheet activity or look at individual cell activity, however, I want to see those dates side by side. I need to be able to show what I have scheduled for something and then what the new schedule is in my sheet."

But then in the next comment it sounds like you just want to see how many have changed in the past week.

"I had 50 scheduled, 5 cancelled/rescheduled and 45 installations completed for this week."

If you just want counts of what has changed in the past week, you can use a basic Record A Date automation to record the date any time that particular column is updated and then use some straightforward COUNTIFS to count changed dates that are in the past 7 days.

BethWork

@Paul Newcome

Sorry, to be confusing. Yes, in this scenario I start off the week with 100 installations scheduled. As the week progresses, stores might either be cancelled or rescheduled. I have my master source sheet set up and it's linked to dashboards, reports, data meshes, dynamic views, and other sheets. This is the main sheet where schedule information are shared out to my leadership, too.

I'd like to know that I started with 100 installations scheduled this week, but 25 were not completed and now we only completed 75. Since the dates change daily I really only know in the moment where we stand and not what the total scheduled was unless I'm jotting that information down before the week starts.

Paul Newcome

@BethWorkIt sounds like you need a copy row automation going on so that you can lock in the original data as historical.