Locking Values in Row

Art Schneiderheinze

I have a column on Sheet 1 that has a formula that references a value on Sheet 2. If the value on Sheet 2 changes,  obviously the cell in Sheet 1 updates automatically. 

I have another cell in Sheet 1 that is a dropdown. One value in the dropdown is "Completed".

The values in Sheet 2 may change once a year, as we adjust payment amounts for tasks.

Is there a way that once the dropdown cell in Sheet 1 is changed to "Completed" all of the values in the row are locked, meaning the value in any cell (that references cells in Sheet 2, are locked and are no longer updated, even if referenced cells in Sheet 2 change?

Thanks!

Andrée Starå

Hi,

Probably yes, you could set up a rule in the Alerts & Actions to lock the row when Completed, but I haven't tested if the links can still update it or not.

If you try it (before me), I'd appreciate it if you answer what the result was. TIA!

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Andree

 

Cell references =[Column Name]3 and cell linking from other sheets WILL still automatically update even if the row is locked. I tested this as an option when trying to figure out a way to Date Stamp when a task is completed.

 

Also...

 

The TODAY() function will only update when the sheet becomes active. However... "Active" does not necessarily mean "Open". If a new row is added via a form, it will update the TODAY() function without having to open the sheet. Likewise, if a cell on the TODAY() sheet references or is linked to a cell on another sheet, if the cell on the other sheet updates thus updating the sheet reference or cell link on the TODAY() sheet, the TODAY() function will update without having to actually open the sheet.

 

Update requests and changes made to a report pulling from that sheet both will update the TODAY() function as well. 

 

So as I stated above... The sheet must become ACTIVE but doesn't necessarily have to be opened. 

 

P.S. Having the sheet "Viewed" on a dashboard even as a URL Widget doesn't count. You would have to edit something on the sheet to activate the change (which can be done if you use the link for an "Edit By Anyone" type of publishing in the URL widget on a dashboard. I display multiple sheets on one dashboard at times just so I can track x-sheet references and whatnot when I am tying a bunch of sheets together.

MeganJones

Were you ever able to resolve this?

I have a similar scenario. I am using the INDEX/MATCH formulas to assign a person to a task based off of a state assignment list. Once the task is completed, I do not want that person's name to change, even if the state assignment changes in the future. Any help is appreciated, thanks!

Sophie Knudson

I'm curious if this worked!

Andrée Starå

Hi @MeganJones & @Sophie Knudson

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

https://community.smartsheet.com/discussion/comment/273070#Comment_273070

Opps! I thought I saw an email where it was live. I flagged it for deletion. Thanks for catching that!

Andrée Starå

@Paul Newcome

Easy to miss!

You're more than welcome!

Tim Starkey

Have a similar scenario, and wondering if the solution from @Andrée Starå will work for us.

I have a proposal fee worksheet as a template. Users save this template into a proposals workspace for each new proposal. On the task lines users enter staff role (PM, Designer, etc), and # hours. Fee worksheet then uses a Vlookup on a Billing Rate table to calculate the task fee. Once proposal is approved and the contract opened, I would like the fee worksheet to stop updating. Billing rates are adjusted each year. When I go into an old fee worksheet that was 'approved', I want to see what the fees were at the time of approval (based on the billing rates at that time). I don't want it to update the fees based on the current billing rate.

Ideally, I would like the users to do something simple, like a cell pulldown that says 'approved', and then the sheet stop updating cross sheet formulas. I have tried to think of ways around it:

1. Automation that replaces the cross sheet formulas with the static value (similar to when you use paste values in excel)
2. Archive the sheet? I am not familiar with this, but I understand it stop values updating.

I would prefer to avoid a process that requires making a new sheet.

Thanks!

Tim

Paul Newcome

@Tim Starkey To avoid creating another sheet, you could update your table in the Billing Rates sheet to track historical dates by inserting a date type column and entering the "effective" date for that particular fee.

Then in the Fee Worksheet you could insert a Created (Date) type column to capture when each row is created and use this along with an INDEX/COLLECT in place of your VLOOKUP to pull the appropriate rate.

Technically the cell links/cross sheet references would still be "live" but because we are leveraging the date the worksheet was created it shouldn't pull something more recent.

Tim Starkey

Thanks @Paul Newcome , Been busy and just got back to this issue. I will give it a test and see if that works!