Can locking rows also freeze formulas from updating when conditions are met?
I have a sheet that I am using formulas to pull in data for various dates (IE totals based on COUNTIFS formulas). The records that the data is coming from will continually update so in the example below what I would see is on 8/1/21 the number being 100 and the next day for that date the number being 50. Ideally, I want whatever number is in a given date row to be frozen once the date is in the past. So 8/1 would continue to be 100 even if 50 of those records have a change that would make them no longer be counted by the COUNTIFS formula.
Does locking rows allow for this
Example:
Column1 Column 2
8/1/21 100. <-- this number is "locked" or frozen once we are past the date in column1
8/2/21 300
Best Answer
-
No, formulas will continue to calculate even if the row is locked.
However, you could set up a copy row automation based on the date field, that once the date is in the past it copies the data to a separate, historical sheet. The copy row automation will copy the value of the formula without bringing over the formula. Then you can base your calculations or widgets off of this second helper sheet. Would that work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
No, formulas will continue to calculate even if the row is locked.
However, you could set up a copy row automation based on the date field, that once the date is in the past it copies the data to a separate, historical sheet. The copy row automation will copy the value of the formula without bringing over the formula. Then you can base your calculations or widgets off of this second helper sheet. Would that work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I hope you're well and safe!
To add to Genevieve's excellent advice/answer.
Here's a possible workaround or workarounds
- Please have a look at my post below with a method I developed to store/lock the value.
More info:
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. 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.
-
Thanks all for the suggestions. I ended up using a different workaround based on data stored in one of our systems and doing a data shuttle to pull in the change history. However, that will not work for some of my upcoming projects so I will be using your suggestions for those. I appreciate the help!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!