This is a little late, but I came across it in a search so it might help someone else - try something like this:
=IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!P:P"),
IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!P:p"),
IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!p:P"),
ImportRange(SpreadSheet_GUID,"Bookings!p:p"))))
Basically the idea is to force Google to recalc using variations of the original address (upper/lower case column letters).
Answer from Tim on Stack OverflowThis is a little late, but I came across it in a search so it might help someone else - try something like this:
=IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!P:P"),
IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!P:p"),
IFERROR(ImportRange(SpreadSheet_GUID,"Bookings!p:P"),
ImportRange(SpreadSheet_GUID,"Bookings!p:p"))))
Basically the idea is to force Google to recalc using variations of the original address (upper/lower case column letters).
The problems with the IMPORTRANGE() function has been somewhat of an ongoing issue. There have been several bugs over the years and it is up to Google to find a stable solution so it works as designed.
Until then, this might help you:
Instead of editing the cell, creating a new spreadsheet, or doing anything else to have the sheet reattempt the function (almost like playing lottery), you can force Google sheets to reattempt the function automatically when it fails to load:
Wrap the IMPORTRANGE() function that is causing problems with an IFERROR() function. That way, the sheet attempts the import, and only if it fails, it attempts it again. The function can be nested to attempt several times in a row.
You can add named ranges to your source data (right-click-source>define-named-range) to facilitate. For instance, you can create 3 different named ranges for the "J:J" range. Call them "J", "Ja", and "Jay", then you call on your IFERROR() function in the sheet you want to import to:
=IFERROR( IMPORTRANGE( "SheetID","J" ), IFERROR( IMPORTRANGE( "SheetID","Ja" ), IFERROR( IMPORTRANGE( "SheetID","Jay" ), IFERROR( IMPORTRANGE( "SheetID", "'TabName'!J:J" ), IFERROR( IMPORTRANGE( "SheetID", "'TabName'!j:j" )))))
This formula will attempt your import and deliver it if it succeeds, and if not reattempts a total of 5 times in a row automatically, which doesn't guarantee you will succeed, but if you have a 50% chance of success with your imports, nesting 5 in a row should give you a 96% chance, which is a lot better. My personal experience has been that it hasn't failed since I've done the IFERROR() nest.
Hope it helps.
Google Sheets - #REF! Error IMPORTRANGE - Google Docs Editors Community
IMPORTRANGE Broken
Query/ImportRange resulting in #REF! error, even when using correct syntax? - Google Docs Editors Community
google sheets - IMPORTRANGE #REF! Error - Web Applications Stack Exchange
Does IMPORTRANGE have a data limit?
Is there any difference between IMPORTRANGE and IMPORTDATA?
How to split Google Sheets?
Videos
I am creating a Google spreadsheet for work. I am starting with a master sheet. I duplicate that sheet, rename it for the type of product, delete all data that doesn’t apply (aside from header row), repeat for 4 other products. (Also, I should add it’s easiest to keep all of the info in one master sheet for importing the original data as well as referring back to it. Each team lead will only be responsible for inputting data on their individual tab.) Now for the issue. I am using IMPORTRANGE to bring data from specific rows/columns to the master sheet. I am not using any additional formulas or formatting. I am getting a #REF! error that states “Reference does not exist”. The reference clearly does exist but only in two of the 17 cells. I am new to this formula and have tried everything to get this to work for almost 2 hours now. For reference here is the formula I’m adding on to the link for the spreadsheet: , “Leather!G" & ROW() & ":AP" & ROW() ) I am happy to share additional info as needed to get a resolution.
EDIT: the problem resolved itself. Not sure if it was because I opened the dashboard on mobile, if google fixed a bug, or it was a fluke - but it seems to be working now. some great troubleshooting tips in the comments below if you come across this post experiencing similar issues.
Anyone having issues with IMPORTRANGE this week? I have a dashboard sheet that pulls some data from 4 other identically structured sheets owned by 4 other users. It's worked flawlessly for more than a year. Just this week I noticed one source giving the #REF! Import Range internal error. message. When I make a change to any of the source sheets, then the IMPORTRANGE on the dashboard breaks. If I view the version history of the source sheets, the current version says "Google Docs encountered an error. Please try reloading this page, or coming back to it in a few minutes." If I revert the sheet to a previous version then the IMPORTRANGE works, but if I change even one cell on the source sheet, the dashboard breaks again. I've tried reverting all the sheets including the dashboard to previous versions, and it works until I make any change.
As a random test, I created a brand new sheet with my personal account, created an import range reference to another brand new sheet with my personal account, and it works - until I refresh and then it breaks there too. so it doesn't seem to be related to permissions, my account, my browser (tried 3 different PCs), or the sheets themselves. is google sheets having some heartburn this week?
Hi guys,
Sorry for what's probably a stupid question but, I'm trying to use the IMPORTRANGE function to reference a cell in another workbook, and my formula isn't working. Can anyone see what's wrong with this, please?
=IMPORTRANGE("URLToMyOtherSpreadsheet","'Interest Log'!C2")
Thank you! Sorry, I really tried to Google around and solve this before asking, but I'm running into a wall.
Hello all,
I've been using Google sheets as an integral part of our daily business operations for the past 2 years. We have a series of spreadsheets created, many of which call on values from other sheets using the =IMPORTRANGE function.
I know this function has its quirks, but for the most part has worked great for us. So our sheets have been created and confirmed to be working perfectly fine long ago, and we continue to use them daily.
We would occasionally get the random "#REF!" error in an IMPORTRANGE cell, which would typically be cleared up by a hard-refresh of the browser/sheet. However, for about the past week now... the #REF! errors have overtaken many cells, and browser refreshes are doing nothing to solve the issue.
If I remove the formula entirely, and then re-add it to the same cell with the IMPORTRANGE function.. the cell populates perfectly fine with correct numeric values. So it's somehow temporarily "broken", even though the formula is correct.
I know this issue is documented many places around the web, and I've researched many of the "fixes" for it, including wrapping the NOW() or GOOGLECLOCK() function around any IMPORTRANGE references. I've tried several of these fixes, with nothing working.
Any help on this would be greatly appreciated, as it's driving me insane at the moment :)