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.

Marvin Brown
Marvin Brown ✭
edited 12/09/19 in Archived 2015 Posts

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?

Tags:
· Share on FacebookShare on Twitter

Comments

  • Travis
    Travis Employee
    edited 06/03/15

    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. 

    · Share on FacebookShare on Twitter
  • Marvin Brown
    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.

    · Share on FacebookShare on Twitter
  • Spencer Marlow
    Spencer Marlow ✭
    edited 09/16/16

    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.

    · Share on FacebookShare on Twitter
  • Ramsay Zaki
    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).

     

     

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    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

    · Share on FacebookShare on Twitter
  • Randy Van Winkle
    Randy Van Winkle ✭✭✭

    Has this issue been resolved within Smartsheet yet?

    · Share on FacebookShare on Twitter
  • GarryD
    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.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories