Google Sheets: Cannot find range or sheet for imported range
Last verified
#REF! Cannot find range or sheet for imported range.What it means
IMPORTRANGE connected to the source spreadsheet, but the range string in the second argument does not point to anything that exists there. Access is fine; the address inside the file is wrong.
The range string must be the tab name, an exclamation mark and a cell range, for example "Orders!A1:F500". If the tab name contains spaces or special characters it must be wrapped in single quotes inside the string: "'Q1 Orders'!A1:F500".
When this appears on a formula that used to work, someone almost certainly renamed or deleted the source tab or a named range it referenced.
What it looks like
=IMPORTRANGE("1AbC...xyz", "Sales Data!A1:D100")
#REF! Cannot find range or sheet for imported range.
=IMPORTRANGE("1AbC...xyz", "'Sales Data'!A1:D100")Why it happens
- The tab name is misspelled, has different capitalization habits, or has a trailing space.
- A tab name with spaces is not wrapped in single quotes.
- The source tab was renamed or deleted after the formula was written.
- A named range used in the formula was deleted or renamed in the source file.
- The range string was built with string concatenation and produces an unexpected value.
How to fix it
- Open the source file and copy the tab name exactly, including spaces.
- Wrap names that contain spaces in single quotes: "'Sales Data'!A1:D100".
- If you build the range string in a cell, check that cell's value directly to see the exact text being passed.
- Check the source file's version history to see whether the tab was renamed recently, and update the formula.
- Test with a simple range such as "A1" (which reads the first tab) to confirm the connection itself works.
How to stop it recurring
Use a named range in the source file and reference it by name, so tab renames do not break imports. Agree with source owners that tabs feeding other files are not renamed without notice, and keep a data-dictionary tab that lists which external files depend on which ranges.
Official documentation: support.google.com/docs/answer/3093340. See all Google Sheets error messages or the Google Sheets limits and quotas.
Related errors
Definitions
Quick answers
What does "Cannot find range or sheet for imported range" mean in Google Sheets?
IMPORTRANGE connected to the source spreadsheet, but the range string in the second argument does not point to anything that exists there. Access is fine; the address inside the file is wrong. The range string must be the tab name, an exclamation mark and a cell range, for example "Orders!A1:F500" .
How do I fix "Cannot find range or sheet for imported range"?
1. Open the source file and copy the tab name exactly, including spaces. 2. Wrap names that contain spaces in single quotes: "'Sales Data'!A1:D100". 3. If you build the range string in a cell, check that cell's value directly to see the exact text being passed. 4. Check the source file's version history to see whether the tab was renamed recently, and update the formula. 5. Test with a simple range such as "A1" (which reads the first tab) to confirm the connection itself works.
When an IMPORT formula cannot read the page
IMPORTXML and IMPORTHTML only see the HTML the server sends. An Autonoly extraction step reads the page in a real browser and writes rows to Google Sheets. Sites that block automated visitors can still block it.
Also see Google Sheets integration.