Detecting Cell Changes, Modifying other cells on update.

Marvin Brown

Hey guys.

 

So, I'm curious if there's a way to do something. I want to detect certain changes in a cell, and modify another cell based on the date those changes occur.

 

The goal here is to automate the entry of dates of completion. I've thought about entering the following formula in a date cell: =IF([% Complete]2024 = 1, TODAY(), 0)

 

this formula works, but I'm thinking what it's going to do is just keep updating to be the current day every time the sheet is opened or refreshes, that's not really desireable. I really just need it to update that value once, upon change to 100%, and then never do anything ever again. Any ideas?

Travis

You are correct, TODAY() will change each day. Is this the last change that will occur in the row? If so, you can use the Modified (Date) system column which will show a timestamp of the last time the row was modified. You can also view the history of the % Complete cell (or any cell) by right clicking in it and selecting View History. This will show you the complete history of the cell including any change that was made, what the change was, when the change was made, and who made the change. 

Marvin Brown

Your solution was fantastic. I can generally assume that no more changes will be made to a row once that task is marked 100%, therefore capturing the date that occurs and then doing some math to determine where it lags behind or beats the assigned due date gives us exactly the data we were looking for. Thanks a million, this will hopefully mitigate some bad assumptions we were making in the past about how long stuff takes.

Spencer Marlow

Glad I searched before I posted this exact same question. I have a situation where Travis' solution will work well (say) 80% of the time ... and I'm going to go with that for now ... but it would be great to bring a clean, watertight solution to this and allow a formula in Cell X to recognise and work with the earliest / latest / median value of the date in the View History value of Cell Y

 

An additional reason for this is that, if you use the System value "Date Last modified", you can't add or delete any columns from your sheet without losing all your timestamp data - as evry row in the sheet is modified and the system date therefore reset across all rows to the time of that blanket modification.

Ramsay Zaki

Has anyone found an alternate solution?

Just like Spencer mentioned - using Modified Date is not a viable option because I cannot add a new column to the sheet or change the data on a row in ANY way (otherwise, I end up losing this time stamp).

 

 

J. Craig Williams

Ramsay,

I do this using Zapier for small numbers of sheets and the API for many.

http://ronin-global.com/2016/12/11/smartsheet-fixed-date-upon-completion/

Craig

Randy Van Winkle

Has this issue been resolved within Smartsheet yet?

GarryD

Nope not resolved yet, asked for so many times, yet still nothing.  Craig has pasted is Zapier solution to many times it is unreal because of how much this gets asked for over and over.