Force google sheet formula to recalculate

14
votes

I have an index/match formula that matches a specific file based on the date value of certain cells. Here's the formula:

=IFERROR(INDEX(INDIRECT("'"&TEXT($O$3,"mm-dd-yyyy")&"'!"&"$D3:$D$500"),MATCH($D5,INDIRECT("'" & TEXT($O$3, "mm-dd-yyyy") &"'!$B$3:$B500"),0)),0)

I noticed the values did not change even when I imported a new CSV. Only way I got the values to update was to essentially re-enter the formula by dragging from top to the last cell like one would manually do.

I tried changing the recalculation time under settings, but it seemed like the setting does not apply to my formula, as I set it to every minute and nothing happened.

I thought about writing a script to have it re-enter the formulas and set it to run every day, but I'm hoping that there's a easier way to do this.

3
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

3 Answers

21
votes

Short answer

Your formula is not being recalculated because its arguments do not change. The solution is, as you already figured out by yourself, to re-enter the proper arguments into cells that your formula references to.

Explanation

Google Sheets formulas are recalculated when

  1. The spreadsheet is open
  2. The function arguments changes
  3. The functions NOW, TODAY, RAND, and RANDBETWEEN are updated are according to the spreadsheet settings, on change, on change and every minute, on change and every hour
  4. External data functions recalculate at the following intervals:
    • ImportRange: 30 minutes
    • ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
    • GoogleFinance: may be delayed up to 20 minutes

Note: Some functions and custom functions doesn't allow not deterministic functions as arguments.

References

6
votes

I found an easy solution to my problem. I wrote a script to essentially re-enter the proper dates into cells that my formula references to and the formulas updated.

3
votes

Here's another solution, albeit one that is computationally expensive: pass the range to be considered in the calculation to the function. That way, any time that a value changes in the passed range or that the range itself changes (such as inserting a row within the range), the formula is recalculated.

Example: Try this simple function.

function testPassRange( calcRange ) 
{
    return calcRange.length ; 
}