Why does my Google Sheets formula not automatically recalculate?

Question

I have this formula in a cell in a 'Summary' sheet which is waiting for a new sheet to be created with its name being a date:

=IFERROR(VLOOKUP($A3, INDIRECT("'"&TEXT(BN$2,"m/d/yyyy")&"'!$E$6:$o$100"), 11, false), "")

However, when I add the new sheet with the name that would match, then it doesn't automatically recalculate and pull in the values from the new sheet.

The weird thing is if I add a column to the left of this column that has the formula shown above, then it recalculates ... and all is well. Another weird thing is if I add a column far enough away from the column in question then it doesn't recalculate in the same way that it does when I add a column near the column in question (hmmmm....).

What is going on here? And how can I work around it? Seems Google has some major bugs around this area or something ...

Answer 1

• This is due to INDIRECT.

• A partial workaround:

    =IFERROR(INDIRECT("'"&C9&"'!A1"),RAND())
  

• Use this instead of just INDIRECT in your formula. It still won't update on opening and closing. But it'll update, Whenever there's a edit anywhere in the sheet (making this a double volatile combo with RAND()).

• Also, Try turning on recalculations every 6 hours or so in spreadsheet settings.

PS: Your Spreadsheet might burn due to the heavy volatility.

Answer 2

based on idea similar to TheMaster I defined a named range refresh and refered to it in the else portion of the IFERROR formula. Whenever I create a new sheet, rename an existing one, or feel the need to make sure all indirects are updated I simply edit the content of refresh, and put it back to blank.

=arrayformula(iferror(indirect(B11&"!D42:H42");refresh))

Answer 3

I used Berteh's idea, but found that putting the refresh range within the else portion of the iferror removed the benefit of having a way to have a blank cell if there is no value, or what ever else you'd want to use the else for.

So, to maintain that, I put this together which seems gets us the benefit of preserving the benefit of using iferror and forcing the indirect to update.

=IF(refresh!$A$1=1,IFERROR(VLOOKUP($B68,INDIRECT(D$66&"A1:aa2000"),8,0),""))

Updating the value of the refresh range will force a recalculation (I change the value of cell A1 from 1 to a 0 and then back to 1). I've implemented this on multiple rows across multiple sheets and not found this to blow up the Google Sheet.

Answer 4

I found this post and tried to use the RAND() function to solve this issue as a trigger. I wanted the same idea: resulting BLANK when there is no match. So, my solution was replacing RAND() by IF(RAND()*0=0,"","") With that, I have the trigger and the result as BLANK. It worked!

Answer 5

Sorry for the late response, but I believe I have a solution that works a little better. I was experiencing the same problem with formulas involving almost exactly the same set of formulas.

Basically, just add in a check to see if now() equals an impossible time (0), and when it fails (every time), have it execute the formula that won't update on its own.

=IF(NOW()=0,"",
IFERROR(VLOOKUP($A3, INDIRECT("'"&TEXT(BN$2,"m/d/yyyy")&"'!$E$6:$o$100"), 11, false), "")
)