Skip to content
Sheet ErrorSheet ErrorSheet Error

INDIRECT cannot read another workbook, open or closed

Updated

INDIRECT turns text into a reference in this spreadsheet. It does not open a file. The string '[Budget.xlsx]Sheet1'!A1, a Windows path, or a https://docs.google.com/spreadsheets/... URL is not an address on this grid. The cell is #REF!. The formula bar still shows INDIRECT and the string. People bring this formula from Excel, where INDIRECT of another workbook works only while that workbook is already open in the same session. A closed Excel file is already #REF! there. Sheets has no equivalent of “the other workbook is open.” There is nothing to unlock by opening a second tab. The myth is that a closed-book INDIRECT exists and Sheets is the product that removed it. Excel does not offer that either.

Sheet Error is not Microsoft and not Google. Keep the Google Sheets UI in English. A bad address in this file is INDIRECT #REF!. A named range passed as text, and which sheet that name uses, is INDIRECT name string. A real cross-file formula is IMPORTRANGE access.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. Leave the tab named Sheet1. In A1 type 10. You do not need a second file. The failure is the string.

An Excel-style external reference. In C1 type xlsx. In C2 enter =INDIRECT("'[Budget.xlsx]Sheet1'!A1"). There is no workbook by that name in this file. C2 is #REF!. The tooltip says the text is not a valid cell or range reference, or it names a sheet that does not exist. Leave C2 broken. Creating a tab called Budget.xlsx will not satisfy the brackets. The brackets are Excel’s workbook syntax, not a sheet title.

C2 is #REF!. The formula bar shows =INDIRECT of a string that starts with '[Budget.xlsx]Sheet1'!A1. A1 on this sheet is 10. There is no second workbook.

A URL is not an address either. In C4 enter =INDIRECT("https://docs.google.com/spreadsheets/d/example"). C4 is #REF!. INDIRECT does not fetch. Leave C4 broken.

The same-file control. In C6 enter =INDIRECT("A1"). C6 is 10. Same function, a real address on this file. In C8 enter =INDIRECT("Sheet1!A1"). C8 is 10. The sheet name is a tab here, not a file.

What “open in Excel” would still not do. If you still have Excel, INDIRECT to a workbook that is closed returns #REF! there too. Opening that workbook in Excel can make the formula calculate, and closing it breaks the formula again. Sheets never enters the “open” state for an .xlsx on a disk or for another Google file. Do not plan a workflow that depends on it.

A normal Excel link that is not INDIRECT, the kind that updates from a closed workbook in desktop Excel, also does not stay live after the import. Those cells arrive as values or as #REF!. This page is the INDIRECT string. Power Query connections are Power Query import.

What is actually wrong

Section titled “What is actually wrong”
  • INDIRECT parses text as A1 or R1C1 on the spreadsheets you already have open as this file. A file name, a path, and a URL fail that parse. The tooltip is the same family as a mistyped sheet name. See INDIRECT #REF!.
  • Excel’s extra case, another workbook that happens to be open, is a desktop session feature. It is not a closed-book feature. Copying the formula because a blog said INDIRECT can see other files skipped that condition, and Sheets does not implement the condition anyway.
  • IMPORTRANGE is the function that reads another Google spreadsheet. It needs a URL and a range, and the first use asks you to allow access. It does not read an .xlsx that only lives on a laptop. See IMPORTRANGE access. A chain of imports that loop is IMPORTRANGE cycle.
  • Volatility is a separate cost. A same-file INDIRECT recalculates often. Pointing it at a fake external path does not make it fetch, and it still will not be cheap if you copy the broken formula down a column. See INDIRECT volatile.

Fix

Section titled “Fix”

Leave C2 and C4 as #REF!.

A cell in this file stays INDIRECT, with a real address. C6 is the control. Build the text from a sheet name that exists: if F1 contains Sheet1 and F2 contains A1, then =INDIRECT("'"&F1&"'!"&F2) returns 10 when F1 has no odd characters. A sheet name with a space needs the quotes the INDIRECT sheet name page shows. Do not put a file name in F1.

Another Google Sheet is IMPORTRANGE. In E2 enter =IMPORTRANGE("https://docs.google.com/spreadsheets/d/THE_REAL_ID","Sheet1!A1") with an ID you can open. Allow access when the cell asks. E2 then shows that cell. A made-up ID stays #REF!. Allow access is the permission step, not a closed-workbook switch. See IMPORTRANGE access.

E2 is an IMPORTRANGE formula, either asking for access or showing a value from the other Google Sheet. C2 is still #REF! from INDIRECT of [Budget.xlsx]. C6 is 10 from INDIRECT of A1.

An .xlsx on a computer is an import, not a live INDIRECT. File → Import brings values in once. Later saves in Excel do not flow through C2. Import again, or publish a CSV and use IMPORTDATA, knowing redirects and delimiters are their own pages. See IMPORTDATA redirect.

Do not wrap C2 in IFERROR. You would hide a reference that cannot exist. See IFERROR hides cause.

If the string inside INDIRECT names another file, a path, or a URL, the #REF! is expected. Use a same-file address, or IMPORTRANGE for another Google spreadsheet. Opening something elsewhere will not make C2 calculate.