Can locking rows also freeze formulas from updating when conditions are met?

Danielle O'Connell

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

Genevieve P.

Hi @Danielle O'Connell

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

Genevieve P.

Hi @Danielle O'Connell

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

Andrée Starå

Hi @Danielle O'Connell

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: 

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

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!

Danielle O'Connell

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!

Andrée Starå

@Danielle O'Connell

Excellent!

You're more than welcome!