Yes. Excel’s INDIRECT function can refer to a cell in another workbook, but the source workbook must be open; if it is closed, the formula returns #REF!. A basic example is =INDIRECT("'[Sales.xlsx]January'!A1"), where Sales.xlsx is open and its January sheet contains the value to return.
What INDIRECT does with an external reference
INDIRECT takes text that looks like a cell reference and interprets it as a real reference. This lets a formula assemble a workbook, sheet, or cell address from text or from values in other cells. It does not open a workbook or retrieve data from a closed file. That distinction explains both the function’s usefulness and its main limitation.
A regular external link points directly to a cell, for example a formula Excel creates when you link to another open workbook. INDIRECT, by contrast, receives the address as text. Because Excel cannot follow that text to a closed workbook, the source must be open when the formula calculates. This is true even if the text contains a complete file path.
The function is useful when the reference itself needs to change—for example, when a user chooses a sheet name from a drop-down list. If the workbook and target cell are fixed, a normal external reference is usually more dependable and easier to maintain.
Basic syntax and reference format
The function’s syntax is =INDIRECT(ref_text,[a1]). The required ref_text argument is text representing a reference. The optional a1 argument specifies whether the text uses A1-style references; if omitted, Excel assumes A1 style. External workbook references ordinarily use A1-style notation, so the optional argument is not needed in the examples below.
For an external reference, the text generally follows this pattern: '[Workbook.xlsx]Sheet name'!Cell. The single quotation marks enclose the workbook and worksheet portion, the square brackets enclose the workbook name, and the exclamation mark separates the sheet from the cell. Excel requires quotation marks around names containing spaces or certain special characters; using them consistently helps avoid parsing problems.
With Sales.xlsx open, the January worksheet active or otherwise available in that workbook, and the target value in A1, this formula returns that value:
With Sales.xlsx open and the value in January!A1: =INDIRECT("'[Sales.xlsx]January'!A1"). If A1 contains 250, the formula returns 250.
The formula itself can be entered in a different workbook. The reference string names the source workbook; it does not have to be the workbook that contains the formula. Both workbooks need to be open for the external INDIRECT reference to resolve reliably.
Build a reference from cell values
A common reason to use INDIRECT is to let a worksheet selection determine which source sheet to read. For example, put the open source workbook’s name in B1, the worksheet name in B2, and the cell address in B3. If B1 contains Sales.xlsx, B2 contains January, and B3 contains A1, use this formula in the destination cell:
With the workbook name in B1, sheet name in B2, and cell address in B3: =INDIRECT("'["&$B$1&"]"&$B$2&"'!"&$B$3). If Sales.xlsx is open and January!A1 contains 250, the result is 250.
This formula joins several pieces of text to create the same reference as the fixed example. The ampersand joins text, while the quotation marks inside the formula supply literal punctuation for the final reference. Changing B2 to February redirects the formula to February’s A1 cell, provided that sheet exists in the named open workbook.
For a selection list, use Data Validation to make the sheet-name cell easier to control: on Windows, choose Data > Data Validation and set Allow to List. A validated choice reduces typing errors, but it does not make a closed workbook accessible or automatically confirm that every listed sheet exists.
When a full file path is needed
If the source workbook is open but Excel cannot identify it by its file name alone, include its folder path in the reference text. The path is placed before the workbook name, while the workbook remains inside square brackets. For a Windows folder such as C:\Reports, the reference text takes the form 'C:\Reports\[Sales.xlsx]January'!A1.
With the source open at C:\Reports\Sales.xlsx and the value in January!A1: =INDIRECT("'C:\Reports\[Sales.xlsx]January'!A1"). If that cell contains 250, the result is 250.
Folder locations and workbook names must match the actual file. Network drives, synced folders, and files opened from a URL can have different path representations, so copying a working external reference from Excel is safer than guessing the path format. A full path only identifies the workbook; it does not remove the open-workbook requirement.
Important limits and behavior
- The source must be open. If the external workbook is closed,
INDIRECTreturns#REF!. Reopening the source may allow the formula to calculate again. - The reference is text. Excel generally cannot update text inside the formula when a referenced sheet or workbook is renamed. A text-built address may therefore stop working after a rename.
- Names must match. A misspelled workbook name, worksheet name, or cell address can produce
#REF!. Extra spaces in a cell that supplies a name can also make the assembled reference invalid. - INDIRECT is volatile. Excel recalculates volatile functions more often than many ordinary formulas. A large workbook filled with complex
INDIRECTformulas can recalculate more than necessary and may feel slower. - It is not a data-import tool. The function returns a reference result; it does not copy a table, preserve a history of values, or refresh a dataset from a closed file.
These constraints matter when designing a shared workbook. A formula that works on one computer because a particular source file is open may show an error for another person who has not opened that file or whose copy is stored under a different path.
Choose between INDIRECT and other methods
Use INDIRECT when a valid reference must change based on a cell value and the source workbook can remain open. For example, a reporting workbook might let a user select a month and display one corresponding cell from the matching sheet. Keep the selection values controlled and test the formula with each allowed workbook and sheet name.
For a fixed cell or range, use a direct external link instead. Excel can create one by opening both workbooks, typing an equals sign in the destination cell, selecting the source cell, and pressing Enter. Direct links are easier to inspect and do not rely on INDIRECT parsing a text string. External links have their own maintenance and update considerations, but they do not share this particular open-source limitation of INDIRECT.
If the goal is to combine or import tables rather than calculate a changing cell reference, consider Power Query. In Excel for Microsoft 365 on Windows, the usual route is Data > Get Data > From File > From Excel Workbook. Choose the source workbook and the relevant table or sheet, then load the data. Power Query is designed for importing and refreshing data; it is not a drop-in replacement for a formula that must return one dynamically selected cell.
For a same-workbook sheet selection, INDIRECT can be appropriate without involving external-file behavior. A direct reference remains preferable whenever the target is known and does not need to change. Choosing the simplest method reduces broken references and makes formulas easier for another person to audit.
Troubleshoot common errors
If the formula returns #REF!, first open the source workbook. Then check that the workbook name, sheet name, and cell address in the text match exactly. If a name comes from a cell, look for leading or trailing spaces and verify that the formula is constructing the expected string. Temporarily putting the assembled reference in a helper cell can make punctuation and spelling errors easier to spot.
If Excel displays the formula as text rather than calculating it, check whether the destination cell is formatted as Text. Change its number format to General, then re-enter the formula. Also confirm that the entry starts with an equals sign and that Excel is set to calculate formulas; in desktop Excel, calculation options are available from Formulas > Calculation Options.
If the formula works only while a workbook is open, that is expected for an external INDIRECT reference, not evidence that the path is incomplete. Adding a full path does not make a closed workbook readable through this function. Replace the formula with a direct external link or use an import workflow if the file must remain closed.
Frequently Asked Questions
Can INDIRECT reference a closed Excel workbook?
No. An external workbook referenced through INDIRECT must be open. If it is closed, the formula returns #REF!, even when the reference text includes the workbook’s full path.
Does INDIRECT work between two workbooks?
Yes, when the referenced workbook is open and the workbook and sheet names in the text are valid. The formula may be stored in either workbook, but the external source still needs to be open.
Can I use INDIRECT to choose a worksheet dynamically?
Yes. Put a valid sheet name in a cell and join it into the reference text. The selected sheet must exist, and the assembled reference must use the correct quoting and punctuation.
Will INDIRECT update if I rename a worksheet?
Do not rely on it to update text that contains the old name. Because the address is supplied as text rather than as a normal tracked reference, a rename can leave the formula pointing to a name that no longer exists.
What should I use instead if the source workbook stays closed?
For a fixed cell, use a direct external workbook link. For importing and refreshing tabular data, use Power Query. The right choice depends on whether you need a formula result or a refreshed copy of source data.
Is INDIRECT available in Excel for the web?INDIRECT is an Excel worksheet function, but external-workbook behavior can depend on how the files are opened and supported in the web environment. If a formula depends on an external workbook, verify it in the environment where the workbook will actually be used; desktop Excel also requires the source workbook to be open for this kind of reference.
Related
- Gujarati Data in Excel: Typing, Sorting, and Importing
- Excel Data Analysis: How to Find Useful Insights
- Is Excel Free? Microsoft 365 Plans and Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Excel Without Decimals: Formula or Formatting?To round a value in Excel to the nearest whole number, use =ROUND(A1,0) when the value is in…
- TRIM in Excel for Text Cleanup: What It RemovesIn Excel for Microsoft 365 on Windows, use TRIM to remove ordinary spaces at the beginning and end…
- Excel Year Quarter Formula: Get Year and Quarter from DatesWith a valid Excel date in A2, use =ROUNDUP(MONTH(A2)/3,0) to return its calendar quarter as a number from…
- SUMIFS Won’t Work: Causes and FixesSUMIFS usually fails because its ranges do not line up, its criteria do not match the values in…
- LEFT and LEN Excel Formula: How to Use Them TogetherTo remove the last character from text in Excel, combine LEFT and LEN like this: =LEFT(A2,LEN(A2)-1). With the…
- Number Sequence in Excel: How to Continue a SeriesTo continue a number sequence in Excel, enter the first two values, select both cells, then drag the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.