Locking Values in Row
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!
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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!
-
I'm curious if this worked!
-
Hi @MeganJones & @Sophie Knudson
Please have a look at my post below with a method I developed.
More info:
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Opps! I thought I saw an email where it was live. I flagged it for deletion. Thanks for catching that!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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:
- Automation that replaces the cross sheet formulas with the static value (similar to when you use paste values in excel)
- 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
-
@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.
-
Thanks @Paul Newcome , Been busy and just got back to this issue. I will give it a test and see if that works!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!