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.

Is there a formula I could create to do that? I'm 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.

It seems like a lot of work but its doable and the only way I know of that could quickly track historical changes using formulas and in real time.

BethWork

@Cory Page Yes, 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.

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)

Then I update my previous count example: 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 Page If you are not copying data, then how are you capturing the historical data?

Cory Page

@Paul Newcome Based 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 Page I must have been typing a the same time as @BethWork because I didn't see that comment.

@BethWork Are 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

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