Formula Errors

Fix INDIRECT and ADDRESS #REF! Errors Across Spreadsheets

Steve K Oct 12, 2026 872 views

Question details

The user is encountering a #REF! error when using the INDIRECT and ADDRESS functions to dynamically reference a cell in another worksheet.

How to Fix INDIRECT and ADDRESS #REF! Errors Across Worksheets

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating dynamic cross-worksheet references using a combination of INDIRECT and ADDRESS functions.
Observed behavior: The formula returns a #REF! error instead of the referenced cell value because the worksheet name in the text string is missing the required exclamation mark (!).

Before you start

Ensure the referenced worksheet exists in your workbook and that its name is spelled exactly as it appears in the formula.

Solution 1Recommended

Correct the Syntax by Adding an Exclamation Mark

Fix the #REF! error by ensuring the worksheet name is followed by an exclamation mark (!) within the INDIRECT function.

When combining the INDIRECT and ADDRESS functions across worksheets, spreadsheet software requires exact text syntax. If the exclamation mark (!) is omitted after the sheet name, the software cannot distinguish the sheet name from the cell reference, resulting in a #REF! error.

Locate the faulty formula

Select the cell containing the formula that is returning the #REF! error.

Check the worksheet reference string

Look inside the INDIRECT function in the formula bar. Ensure your sheet name string ends with an exclamation mark. For example, instead of "PNWS", it must be "PNWS!".

Update the formula

Modify the formula to include the exclamation mark. For instance, adjust it to: =INDIRECT("PNWS!"&ADDRESS(ROW(AT42),COLUMN(AT42)-2)).

Apply changes

Press Enter to execute the corrected formula. The #REF! error will resolve and display the correct referenced cell value.

Correct the Syntax by Adding an Exclamation Mark

Handling Spaces in Sheet Names: If your referenced worksheet name contains spaces (e.g., 'Sheet 1'), you must also wrap the sheet name in single quotes before the exclamation mark, like this: "'Sheet 1'!".

Advanced Formula Support

Easily Fix and Manage Complex Formulas in WPS Spreadsheet

WPS Spreadsheet supports a full library of advanced functions, including INDIRECT and ADDRESS. You can seamlessly troubleshoot cross-sheet references and formula errors with its highly compatible and user-friendly formula evaluation tools.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the cross-sheet references.
2. Edit your formula: Click the cell displaying the #REF! error and place your cursor in the formula bar.
3. Correct the syntax: Ensure the sheet name in your INDIRECT function has an exclamation mark (!) appended to it.
4. Calculate and verify: Press Enter to apply the formula. WPS Spreadsheet will automatically calculate the correct cell value across your worksheets.

100% compatibility with Microsoft Excel formulas, functions, and .xlsx file formats.Smart formula bar helps you quickly locate syntax issues like missing exclamation marks.Lightweight and runs smoothly, ensuring instant calculations even in heavily cross-referenced workbooks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDIRECT function return a #REF! error even when the exclamation mark is present?

This can happen if the referenced worksheet is in a closed external workbook (INDIRECT requires external workbooks to be open), if the sheet name is misspelled, or if the sheet name contains spaces but lacks enclosing single quotes.

Can I use cell references for the worksheet name instead of typing it directly?

Yes. If cell A1 contains your sheet name, you can write =INDIRECT("'" & A1 & "'!" & ADDRESS(...)) to dynamically reference the sheet without hardcoding the name in the formula text.

Is there a simpler alternative to combining INDIRECT and ADDRESS?

Often, you can use the INDEX and MATCH functions to look up values across worksheets. This combination is less prone to text-formatting syntax errors and calculates faster than the volatile INDIRECT function.

Excel & Data Problems