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 ...
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.