I have a Google Sheet that has one 'master sheet' set up to pull in data using formulas that reference a variety of 'feeder sheets'. The feeder sheets are a landing spot for data that comes from a BI dashboard via scheduled exports every hour. I have never had any issued with the data populating in the feeder sheets correctly (so in my mind, this is not a dashboard issue.) On the master sheet, all of the formulas that I set up to pull in data from the feeder sheets basically work correctly, but they break from time to time.
For example - Today I went into the sheet and noticed that many of my formulas were returning nulls. I checked the relevant feeder sheet and confirmed that the data is still there as expected. I then manually refreshed one of the cells (changed the formula, pressed enter, changed the formula back, pressed enter) and it immediately fixed the issue. Then I happened to notice that while I was in the sheet at exactly 1:30, the next batch of scheduled exports ran triggering the feeder sheets to refresh, and all the remaining broken cells immediately fixed themselves.
basically my takeaway is the following:
- There's no problem with the BI dashboard export / feeder sheets, as far as I know the data is populating correctly 100% of the time.
- There's nothing INHERENTLY wrong with the formulas. Sometimes they break, but I can always fix them by refreshing the cell. Also, it's not the same cells/formulas that break; seems to happen "randomly" to different cells each time.
- It seems like some sort of issue with Google Sheets cacheing? This is the part where I am stumped, obviously.
Bonus point: I'm not 100% sure yet, but it seems like the troublesome formulas use min/max and explicit sheet references, for example "=MAX('SheetName'!A:A)" . I have a bunch of VLOOKUP references, those don't seem to break from what I can see. Interesting that only the min/max formulas break? Again, just a theory at this point, I'm not 100% if this is the case. I may have to remove the min/max formulas but that would require a lot of rework, so I'd prefer to just deal with the root problem if I can.
Thoughts? Suggestions?
-Struggling on a Friday