Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Detecting Cell Changes, Modifying other cells on update.
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?
Comments
-
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.
-
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.
-
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.
-
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).
-
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
-
Has this issue been resolved within Smartsheet yet?
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives