Try the example, then check the result.
Quick answer: Select the #REF! cell and read its full message. If it asks to connect two Sheets you are allowed to use, review the source and destination permissions, then choose Allow access once. If it reports missing permission, a missing range, or an occupied output area, fix that specific cause instead.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
The cell error `#REF!` is a clue, not a diagnosis. For IMPORTRANGE, read the message attached to the cell: a first-time Connect these sheets prompt differs from missing source access or an invalid range. This fictional exercise imports four orders and 38 units from one native Google Sheet into another.
Download the fictional source CSV and independent answer check. The CSV is only a seed; it is not a Google Sheet or an IMPORTRANGE source URL by itself.
Build the two practice files
- Import
source-orders.csvinto a new Google Sheet. Rename its tab to Orders. Confirm the three headers and four rows occupy A1:C5. Keep the practice data fictional. - Create a second, blank Google Sheet as the destination. Copy the URL of the native source Sheet. If you instead opened an uploaded Excel
.xlsxin Drive, use Google's Save as Google Sheets conversion and copy the new native file's URL. - In destination A1, enter this formula, replacing
SOURCE_URLwith the real URL of your new native source:
=IMPORTRANGE("SOURCE_URL","Orders!A1:C5")Google's IMPORTRANGE syntax takes a quoted spreadsheet URL and a quoted range string. The tab name Orders must match your source exactly. A locale may use semicolons instead of commas; follow the formula editor while preserving both arguments.
Choose the fix from the exact message
- The message offers “Allow access.” This is the expected first-time connection prompt for a source you can open. For the two fictional practice files, review who can edit the destination, select Allow access once, and wait. Google's help says an editor of the destination can then use
IMPORTRANGEto pull data from that source; do not connect sensitive source data without checking that access. - The message says you lack permission. Open the source URL under the same Google account. If you cannot open it, request access from its owner. An Allow access button in the destination cannot grant you access to a source you are not authorized to read.
- The message cannot find the sheet or range. Confirm that the source is a native Google Sheet, not the CSV download or an uploaded Excel file. Open the source, read the tab name exactly, and compare it with
Orders!A1:C5. If the tab was renamed, update the formula's quoted range string. - The result cannot expand. Inspect the destination's A1:C5 output area. On your fictional practice copy, remove only cells that would block the imported five-by-three result; do not clear unrelated content in a real report.
- The formula itself is rejected. Check both quoted arguments and the separator required by your Sheets locale. Avoid wrapping the first import in
IFERROR, which can hide the message you need to inspect.
Check the result independently
After an authorized connection, compare destination A1:C5 with the answer check: headers Order ID, Status, Units; then ORD-201/New/12, ORD-202/Packed/8, ORD-203/Hold/3, ORD-204/Shipped/15. Add the quantities independently: 38 units. Seeing no error is insufficient if the wrong source or truncated range was imported.
The CSV input and arithmetic were checked locally. In two private native practice Sheets, a first-time connection prompt appeared and Allow access loaded all four orders in A1:C5. The source rows were pasted from the CSV data; the CSV import dialog, inaccessible-source branch, and XLSX conversion were not tested. Complete those checks only where they apply in your own authorized account.
FAQ
Can I use the downloaded CSV path as `SOURCE_URL`? No. Import it into a native Google Sheet and use that spreadsheet's URL.
Why not keep pressing Allow access? Once files are connected, a later #REF! may be a different problem. Read its message and inspect the source, tab name, and range.
Will the destination update instantly? Google documents load and recalculation delays for IMPORTRANGE. Wait for the source calculation and receiving Sheet to settle before concluding the formula is broken.
The four rows imported, but I still see a problem in my report. What next? A successful connection is a separate milestone. If a filter shows no rows or text lands in the wrong columns, diagnose that result after verifying A1:C5 against the answer check; the related guides below cover those later symptoms.
Source and visual note
Vendor guidance checked 11 October 2026: IMPORTRANGE syntax, permissions, and delays and Office-file conversion. The private two-file connection was reproduced. The video uses illustrative English diagrams, not actual app captures. Do not interpret a drawn connection screen as an app screenshot.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.