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

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!