INDIRECT Excel Not Working? Fix the Reference

If INDIRECT is not working, check that its text builds a valid cell reference, that the referenced sheet or workbook is available, and that the formula begins with an equals sign. Then check the error shown: #REF!, #VALUE!, and #NAME? point to different causes.

What INDIRECT does—and why it can fail

INDIRECT converts text into a cell or range reference. That lets a formula assemble a reference from text stored in cells, such as a worksheet name or an address. It is useful for configurable reports, but the text has to match a reference Excel recognizes exactly. Extra spaces, a missing exclamation mark, or an invalid sheet name can break the formula.

With A1 containing the text B2, this formula returns the value in cell B2 on the current worksheet:

Assuming A1 contains B2 and B2 contains 25:

=INDIRECT(A1)

The result is 25. INDIRECT does not look up a value that happens to resemble an address; it interprets the text as a reference. For example, if A1 contains Bananas, Excel needs a defined name called Bananas or a suitable reference named by that text. Otherwise, the text cannot be resolved.

INDIRECT is also a volatile function. Excel recalculates volatile formulas whenever it recalculates the workbook, even if their apparent inputs have not changed. A few INDIRECT formulas are usually unremarkable, but a large workbook full of them can recalculate more often and feel slower. That is one reason to use it only where a text-built reference is genuinely helpful.

Check the reference text and sheet name

For a sheet reference, the usual pattern is a worksheet name, an exclamation mark, and a cell or range address. Sheet names containing spaces or punctuation should be enclosed in single quotation marks. In a formula that builds the reference, those quotation marks must be included as text.

Assuming A2 contains January and cell B2 on the January sheet contains 150:

=INDIRECT("'"&A2&"'!B2")

The result is 150. The formula assembles 'January'!B2. Quoting sheet names consistently is a good habit even when a particular name has no spaces.

Check these details when a constructed reference fails:

The sheet name in the input cell matches the tab name, including spaces and punctuation.
The address is valid, such as B2 or B2:B10, and does not contain a typo.
The exclamation mark separates the sheet name from the address.
The text does not contain accidental leading or trailing spaces. A cell that looks like January might actually contain an extra space.
Any quotation marks in the formula are balanced and are straight quotation marks, not typographic “curly” quotes.

If a worksheet name is typed by hand, compare it with the sheet tab rather than relying on how it appears in a report heading. A heading can differ from the actual tab name. Also, renaming or deleting a sheet can invalidate text-built references: unlike an ordinary direct reference, text inside INDIRECT is not necessarily updated when a sheet is renamed.

Interpret the error message

The error is a useful clue, though it does not identify every possible cause. First select the formula cell and inspect the formula bar; then check the text being passed into INDIRECT.

#REF! usually means Excel cannot resolve the reference. Common reasons include a misspelled sheet or address, a deleted target, or an external workbook that is closed.
#VALUE! can appear when the reference text is longer than Excel allows. INDIRECT has a 255-character limit for its ref_text argument. It can also result from text that does not form a valid reference in the requested reference style.
#NAME? often means Excel does not recognize a name used in the formula. Check for a misspelled function name, a missing equals sign, or text intended to be a defined name that has not been created.
#N/A is not the standard error for an invalid INDIRECT reference. If it appears, check the other functions in the larger formula, or whether the referenced cell itself contains that error.

When INDIRECT is nested inside SUM, IF, or another function, the displayed error may come from the surrounding formula or from the referenced cells. Temporarily test the INDIRECT part by itself in an empty cell. If it returns the expected reference value, investigate the rest of the formula; if it returns an error, focus on the generated text.

Debug the generated text in small steps

A formula can look plausible while assembling the wrong reference. Make Excel show the text that the formula is building before asking INDIRECT to use it. For a reference assembled from a sheet name in A2 and a range address in B2:

Assuming A2 contains January and B2 contains B2:B10:

="'"&A2&"'!"&B2

The result is the text 'January'!B2:B10. This formula only displays text; it does not retrieve or add the values. Compare that result with the worksheet tab and the intended range. If the text is wrong, correct the input cells or how the formula joins them before adding INDIRECT.

Once the text is correct, use INDIRECT to resolve it. Assuming A2 contains January, B2 contains B2:B10, and the January sheet has the values 10 through 19 in B2:B10:

=SUM(INDIRECT("'"&A2&"'!"&B2))

The result is 145, because the ten values 10 through 19 add to 145. This example also illustrates why checking the assembled text first is useful: a reference error and an incorrect range can otherwise be hard to distinguish inside a longer formula.

Check calculation mode and formula entry

If a formula shows the formula text instead of a result, confirm that the cell is not formatted as Text and that the entry does not start with an apostrophe. Change the cell format to General, then re-enter the formula. Pressing F2 and then Enter can also make Excel parse an existing entry again. If several cells show formulas, the worksheet may be displaying formulas rather than results; press Ctrl+` on Windows to toggle that view. On Mac, the shortcut can vary with keyboard layout; use Formulas > Show Formulas if needed.

If the result does not update after you change a referenced input, check calculation mode. In Excel for Microsoft 365 on Windows, go to Formulas > Calculation Options and select Automatic if appropriate. Manual calculation can leave formulas showing old results until calculation runs. Press F9 to calculate open workbooks on Windows; on Mac, use Formulas > Calculate Now or the shortcut shown in Excel for your keyboard. Calculation mode affects the workbook environment, so do not change it casually in a shared process that intentionally uses manual calculation.

Also make sure a formula uses the argument separator expected by your regional settings. The examples here use commas where a function has multiple arguments, as in English-language Excel on Windows. Some regional configurations use semicolons instead. If Excel rejects a formula as you enter it, consult the separator used by another working formula in that installation rather than replacing punctuation at random.

External workbooks and version details

INDIRECT can refer to another workbook, but there is an important limitation: when the reference text points to a different workbook, that source workbook must be open for INDIRECT to resolve the reference. A formula that works while the source file is open can return #REF! after it is closed. If a report must reliably read from closed workbooks, INDIRECT is often the wrong tool; consider direct links, Power Query, or another approach that fits how the files are maintained.

External-reference text also has to include the correct workbook and worksheet details, and file paths can make the text long and fragile. Moving or renaming a file may break a path assembled as text. Test the formula with the source workbook open and confirm that the workbook and sheet names in the reference match the actual files. Excel for the web has differences in workbook features and external-link behavior; if a formula works on desktop but not in a browser, check Microsoft’s current Excel for the web support for that particular link rather than assuming every desktop scenario is supported.

The core INDIRECT function is available in desktop Excel, including Microsoft 365, but its behavior depends on the reference text and workbook context. The second argument optionally tells Excel whether the text uses A1-style or R1C1-style references. If omitted, Excel uses A1 style. For example, with cell C2 containing 25:

Assuming C2 contains 25:

=INDIRECT("R2C3",FALSE)

The result is 25, because R2C3 means row 2, column 3 in R1C1 notation. If the second argument is FALSE but the text is actually written as A1 notation, the reference will not mean what you intend.

When to use INDIRECT—and when not to

INDIRECT is most useful when a reference must be selected from text, such as a report where a dropdown cell names one of several worksheets. It can also make a formula point to a range whose address is stored in another cell. The benefit is flexibility; the trade-offs are harder auditing, volatility, and sensitivity to spelling and file availability.

For a simple lookup, use a lookup function rather than assembling a cell reference as text. In current Microsoft 365, XLOOKUP can search a list and return a corresponding value; it is also available in Excel 2021. For a fixed calculation, a direct reference such as =SUM(B2:B10) is generally easier to read and maintain than wrapping a text reference in INDIRECT. For large or recurring imports, Power Query may be more suitable than formulas that depend on external workbook paths.

Do not assume INDIRECT automatically adjusts like a normal reference. If a formula contains the text B2, copying that formula down does not turn the text into B3; the text remains B2 unless the formula explicitly constructs a different address. Likewise, inserting or renaming sheets does not make the text itself adapt in the same way a direct reference often does. These behaviors can be useful for deliberate selection, but they can also produce silent wrong results if a workbook is reorganized.

A practical troubleshooting order

Use this sequence to isolate the problem without rebuilding the whole workbook:

Read the exact error or visible symptom. Decide whether the formula is rejected, displays as text, returns an error, or shows a stale result.
Inspect each input cell used to build the reference. Confirm its spelling, spaces, and actual contents.
Display the assembled reference text in a temporary cell and compare it with the intended sheet and address.
Test the INDIRECT portion alone. If it works, inspect the surrounding function and the values in the target cells.
For an external reference, open the source workbook and test again. If that fixes the error, plan for the closed-workbook limitation.
Check cell formatting, formula entry, calculation mode, and the argument separator used by your Excel installation.
Replace INDIRECT with a direct reference or another method if the reference does not truly need to be built from text.

For a workbook used by other people, document which cells supply sheet names or addresses and keep those inputs controlled with data validation where practical. That reduces misspellings, but it does not make an invalid reference valid; the formula still needs a real sheet and address.

Frequently Asked Questions

Why does INDIRECT return #REF! when the source workbook is closed?

INDIRECT generally cannot resolve a text-built reference to a different workbook when that workbook is closed. Open the source file to test the diagnosis. If users need results with the source closed, use direct links or a data-import method that suits the workbook workflow instead.

Why does INDIRECT work for one sheet name but not another?

The failing name may contain a space or punctuation that the constructed reference does not quote correctly, or it may not match the sheet tab exactly. Build the reference with single quotes around the sheet name and inspect the generated text for extra spaces or misspellings.

Does INDIRECT update when I rename a worksheet?

Text inside INDIRECT is not a normal direct reference, so Excel may not rewrite that text when a sheet is renamed. Update the text or the cell supplying the name, then verify the generated reference. Direct references are usually easier to maintain when sheet names change.

Why is my INDIRECT formula not changing after I edit a cell?

Check that the edited cell is actually used in the formula and that calculation is set to Automatic. If the formula is stored as text, re-enter it after changing the cell format to General. In Manual calculation mode, recalculate the workbook to refresh results.

Can INDIRECT use a range instead of one cell?

Yes. Its text can identify a range, and another function can consume that reference. Confirm that the assembled range address is valid; if you are summing it, test the range with SUM and compare the result with the visible values in those cells.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.