Are you encountering the “Cannot find range or sheet for imported range” error message when using the “importrange” formula?
There are situations where you may need to import data from one Google Sheet to another for comparison or further analysis.
However, it can be quite frustrating when you insert the formula and receive the annoying error message.
But fear not!
In this article, we will explore various solutions to resolve this issue and provide step-by-step guidance on how to fix it.
Also read: (opens in new tab)
Enable Smooth Scrolling Google Sheets
How to Use Google Sheets to Mail Merge Labels
How to Print Labels From Google Sheets for Free
Google Sheets Not Sorting Numbers Correctly
Cannot Find Range or Sheet for Imported Range
The “Cannot Find Range or Sheet for Imported Range” error typically happens when you mistakenly use the file or tab name in the formula. To resolve this, make sure you use the correct sheet name instead.
To fix the “Cannot Find Range or Sheet for Imported Range” error in Google Sheets, follow these steps:
Step 1: Use the provided template
To get started, open a text-editing tool such as Notepad. Then, copy and paste the following formula into the text editor:
=importrange(“document key or URL”, "Sheet name!Range")
Step 2: Copy and paste the document key
Once you have pasted the formula, it’s time to insert the necessary data into the formula template.
Begin by pasting the document key into the formula.
To find the document key, open the spreadsheet from which you want to import data.
Look at the URL in the address bar and copy the unique alphanumeric string located between the forward slashes.
You can refer to the screenshot above for visual guidance.
Your formula should now resemble the following:
=importrange(“1mvAuTALDONOdlDQtwOWyxWG55EKDNgXqXvkVlTeY53s”, "Sheet name!Range")
Step 3: Enter the sheet name
Next, you need to enter the sheet name from the spreadsheet.
This step is crucial, and any mistakes may result in errors.
To find the correct sheet name, refer to the provided screenshot.
Remember, you should enter the sheet name, not the file name or the tab name.
By default, the sheet name in Google Sheets is typically set as “Sheet1”.
Therefore, you can simply copy the appropriate sheet name from your file.
However, if your file contains only one sheet, there is no need to enter the sheet name in the formula.
You can skip this step and proceed to the next one.
Nevertheless, I would still recommend following this step and entering the sheet name as a good practice.
Simply double-click on the sheet name, press Ctrl+C to copy it, and then paste it into the formula template.
After entering the sheet name, your formula should appear as follows:
=importrange("1mvAuTALDONOdlDQtwOWyxWG55EKDNgXqXvkVlTeY53s", "Quarter 1!Range")
Step 4: Enter the range
This is the final step after this step, you will have your complete formula ready to be pasted.
Now, enter the range into the formula template.
For example, I want to import the data from cell A1 to cell A11 into my spreadsheet, so the range format will be like this: ! A1:A11
Here’s what the final formula will look like: =importrange("1mvAuTALDONOdlDQtwOWyxWG55EKDNgXqXvkVlTeY53s", "Quarter 1!A1:A11")
If your spreadsheet contains only one sheet and you haven’t entered the sheet name into the formula, your final formula will appear as follows:
=importrange("1uEYB1Y1RlFsY9n1tTPtK2I_cqSGNWTzQmRMQC75ZCcA", "A1:A11")
Step 5: Copy and paste the formula into the sheet
Finally, copy the entire formula and paste it into the appropriate cell in the sheet.
Press the “Enter” key on your keyboard.
You will see the text “#REF!” appear in that cell.
Next, click on the text and select the “Allow access” button to grant Google Sheets permission to access the file.
This will enable the successful import of data from the sheet.
As a result, you will no longer encounter the “Cannot find range or sheet for imported range” error message.
If the above steps do not resolve the issue, try the following additional fixes:
- Double-check the file URL/key to ensure it is correct.
- Confirm that you have the necessary access permissions to the sheet you are trying to import from.
Conclusion
In conclusion, if you’re facing the “Cannot find range or sheet for imported range” error message while using the “importrange” formula in Google Sheets, there are steps you can follow to resolve the issue.
Here’s how to fix the issue:
- Use the provided formula template.
- Copy and paste the document key.
- Enter the correct sheet name.
- Enter the desired range.
- Copy and paste the formula into the sheet and press “Enter”.
- Click on the “#REF!” text and grant access permission.
If these steps don’t resolve the issue, double-check the file URL/key and ensure you have the necessary access permissions.