4

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

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Brian T Hannan
  • 3,925
  • 18
  • 56
  • 96
  • It's due to `INDIRECT`. To make it recalculate at every new trigger would make it too Volatile – TheMaster Oct 05 '17 at 15:27
  • Ok, we are getting somewhere, thanks! Is there a workaround for this? – Brian T Hannan Oct 05 '17 at 15:37
  • Well, I don't know. AFAIK, It should recalculate again on closing and opening. Does it? If it does, Do you really want immediate recalculations? Is it important? If so You might wanna try `Google-apps-script` onEdit trigger or something like that (I'm not experienced in GAS, But if you want help with scripts and I'm right about the problem, add that tag as well). **Or if possible, avoid `INDIRECT` altogether.** – TheMaster Oct 05 '17 at 15:46
  • It doesn't recalcuate on closing and opening. I would have been ok with it if it could do that, but it doesn't. I tried doing it with triggers as well and that didn't work either. I might just have to avoid INDIRECT but that's how I was able to pull the values from each sheet into the summary. Maybe there is another way to do that? – Brian T Hannan Oct 05 '17 at 15:55
  • E6 to O100 has all the results for an event on a given day (the date for the event's day is the name of the sheet). I want to pull each individual event's results into the summary sheet where averages and handicaps are calculated for disc golf. – Brian T Hannan Oct 05 '17 at 17:16

4 Answers4

5

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

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • This worked, with a little tweaking. Thanks so much!! – Brian T Hannan Oct 05 '17 at 17:52
  • 1
    This is the new formula required: =IF(IFERROR(VLOOKUP($A3, INDIRECT("'"&TEXT(BM$2,"m/d/yyyy")&"'!$E$6:$o$100"), 11, false), RAND()*0)=0,"", VLOOKUP($A3, INDIRECT("'"&TEXT(BM$2,"m/d/yyyy")&"'!$E$6:$o$100"), 11, false)) – Brian T Hannan Oct 05 '17 at 17:52
  • My idea was simply adding another iferror inside. `=IFERROR(VLOOKUP($A3, IFERROR(INDIRECT("'"&TEXT(BN$2,"m/d/yyyy")&"'!$E$6:$o$100"),RAND()), 11, 0))` – TheMaster Oct 05 '17 at 18:03
1

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), "")
)
W Arnquist
  • 21
  • 2
  • I was looking to have my values populate when I add a new sheet that matches my indirect. It would auto populate for the existing formula's in the cells that were there in Microsoft excel, but would not in Google Sheets. The RAND solution gave me a bunch of decimal values where I want null or no values until a new sheet is created as I'm summing the column. This solution worked for me. – john stamos Jan 23 '21 at 04:06
0

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))
Berteh
  • 73
  • 8
0

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.

Tund
  • 1