I have 3 Google Sheets, and they are set up as follows (I have changed the names due to confidential data):

SPREADSHEET A: SHEET1

This sheet contains 1600 rows of data, and columns A to AC.
Column C to R are full of VLOOKUP and SUMIFS formulas, and they all reference other local sheets of SPREADSHEET A. It doesn’t contain any volatile functions such as Now() or Today().

SPREADSHEET B: SHEET1

I have an import range formula in Cell A1, as follows: =importrange(“SPREADSHEET A”, “SHEET1!A:R”).
This function takes about 10 seconds to load, and while loading it displays “#REF!” in Cell A1 with an error message of “Import Range internal error.”, then refreshes with the correct data.

Screenshot of SPREADSHEET B Error

SPREADSHEET C: SHEET1

In Cell A1: =importrange(“SPREADSHEET B”, “SHEET1!A1”). The formula returns “#REF!”, just like it’s source sheet SPREADSHEET B, but no error message when you hover over the cell. To me, this is occuring because the importrange formula is not updating once “#REF!” clears from SPREADSHEET B: SHEET1 Cell A1.

Screenshot of SPREADSHEET C Result

I have been using these 3 sheets in this same way with no changes to the formulas or source sheets for over a year with no issues. It is really odd because if I enter =importrange(“SPREADSHEET B”, “SHEET2!A1”) on SPREADSHEET C: SHEET1, I can pull the data. I have tried creating new spreadsheets to importrange from SPREADSHEET B: SHEET1, and it gives me the same result as SPREADSHEET C: SHEET1.

All of my URLs are correct, the importrange formulas are correct, they have worked for over a year with no issue. I do not want to change the way these sheets are set up, the organisation of this information is necessary to be this way.

How can I fix this as our business’ function relies on these 3 sheets?!

New contributor

lunch_enjoyer is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.