Recording the date of a specific update and locking that date?

Evan Meyers

Greetings SMARTsheeters...

 

Not sure if what I'd like to do is possible in SMARTsheet, but here goes...

 

I'm working on a SMARTsheet that receives data adds and modifications from SMARTforms and autoupdate email requests.  There will be various users entering and modifying the data in the SMARTsheet adhoc through these forms and automated alerts to identify any changes in the plan.  We have a team of gatekeepers that will review all of the updates/submissions and approve them.  I'm trying to figure out a way to identify if a row's last change has been approved or if the row is in need of an approval and then (if possible) build some conditional formatting around the cells that have changed and require review/approval.

 

Initially, I was thinking to capture the approval action via a checkbox and writing the date/time stamp of that action to a last approved date field and clearing the checkboxes somehow when another edit was made.  The Today() funciton could work there, but there is no way to lock the date and it would continue to be a moving target each day...additionally, I don't see a way to automatically clear out the approved checkboxes after a new update is made or the approval is captured.

 

I'm thinking this might be possible if I somehow manage the approvals through a report where the approval action is captured on a different sheet so I can compare the last modification dates between sheets, but I'm not sure how I could dynamically capture new row adds to the sheet that would manage the approvals...since my forms create new rows upon submission.

 

Any and all advice on how to handle this kind of situation is more than appreciated!!!

 

Evan Meyers

It seems that I may be able to accomplish this by setting conditional formatting around the "Modified By" system field and coloring rows that were last modified by someone not on the approval team and then turning on highlight cell changes since last logged in...the only problem is that if the approvals are not all complete during a session, the next login, those cells will not be highlighted.  Additionally, that will make any changed cells highlighted for everyone since their last login.

 

Any ideas on a more elegant solution?

JohnHinkle

Have you played around with conditional formatting rules? You can format cells based some aspect of your sheet. They can be formatted based on when the row was last modified, certain checkboxes checked/unchecked, etc or a combination of multiple aspects. 

 

As far as automatically removing checks from boxes... that will be harder. You could build formulas which can remove the check based on something BUT when your originally check the box, the formula would be removed or you would have to build the 'check' into the formula.

 

Reports can be set to automatically add new rows from web forms. If there is not a common aspect of each row you can use as the report criteria, consider add a hidden column (such as a checkbox) to the web form with a default value (checked) then use that checked box as a criteria for your report.  

Gregor

I have a similar question.  Specifically when a cell is changed because an alert & action changed the status to either Approved or Denied in a column, I would like the date of that change to reflect in another column.  Please see screencap 

J. Craig Williams

For small numbers of Sheets, this is what I do:

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

Craig

 

rod_duarte

Hi Craig,

I tried the method you suggested in your article but unfortunately it's not working for me. It gets halted on step 4 but I do not fully understand what is the error about (I am new to Zapier so probably I've set something wrong in the Zap). Do you have any tips that you could share with me?

Thanks.

J. Craig Williams

With Zapier, I just fiddle with it until in bends to my will.

Looks like a blank column is unexpected. I have not seen that particular error recently.

Craig

ps: I am paranoid about posting ID (sheet, row, column) publicly even though someone will intent should not have access to it, even with those IDs.