Force google sheet formula to recalculate

Question

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.

Solution 1

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

• Change a spreadsheet's locale, time zone, recalculation, and language

Solution 2

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.

Solution 3

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 ; 
}

Comments

[Jesse Heines]

Example: Try this simple function. function testPassRange( calcRange ) { return calcRange.length ; }

[Rubén]

add the example to the answer. Comments are mainly intended to criticize / ask for clarification

[zirinisp]

Would be nice if you could share the script with us.

[MIA]

oh thank you so much. just passing an extra parameter with the range data seems to do the trick. function foo(range, throwaway) {do things with range}. call with foo("A1:A100", A1:A100)

[Ryan]

I could not figure out how to get this to work for webapps.stackexchange.com/questions/29283/…