How to Fix Excel INDIRECT #REF! Error with Spaces in Sheet Names
Question details
The user needs to resolve a #REF! error that occurs when using the INDIRECT function to dynamically reference worksheet names containing spaces.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dynamically generating a worksheet name within an INDIRECT formula where the target sheet name includes spaces or special characters.
- Observed behavior
- The INDIRECT function fails to recognize the worksheet name, interpreting the space incorrectly, and returns a #REF! error.
Verify that the target worksheet actually exists in your workbook, the referenced workbook is currently open, and there are no accidental trailing spaces in the sheet name tab.
Add Single Quotation Marks in the INDIRECT Formula
Wrap the dynamically generated sheet name in single quotation marks within the formula string so Excel correctly parses names with spaces.
When an Excel worksheet name contains spaces, special characters, or starts with a number, Excel requires the sheet name to be enclosed in single quotes. Because the INDIRECT function builds cell references using text strings, you must manually concatenate these single quotes into your formula.
Click on the cell displaying the #REF! error to highlight it.
Click inside the formula bar at the top of the window to edit your INDIRECT function.
Add a single quotation mark enclosed in double quotes before the sheet name reference. For example, use "'"& at the beginning of the sheet name string.
Add another single quotation mark right before the exclamation mark that precedes the target cell reference. For example, use &"'!A1" at the end.
Press Enter. Your updated formula should look similar to =INDIRECT("'"&A1&"'!B1"). The #REF! error will disappear if the sheet exists.
Easily Fix Complex Formula Errors with WPS Spreadsheet
WPS Spreadsheet offers an intuitive formula editing interface and full compatibility with complex functions like INDIRECT. Its built-in formula hints make it easy to spot and fix syntax issues, such as missing quotation marks for sheet names with spaces.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .xls workbook containing the broken INDIRECT formula.
- 2. Locate the formula: Click on the cell displaying the #REF! error.
- 3. Edit the syntax: Use the top Formula Bar to enclose the dynamic sheet reference in single quotes, such as INDIRECT("'"&A2&"'!B2").
- 4. Calculate the result: Press Enter to instantly apply the fix and correctly pull the data from the target sheet.
Frequently Asked Questions
Why does the INDIRECT function need single quotes for some sheet names but not others?
Single quotes are only required if the sheet name contains spaces, special characters, or starts with a number. If the sheet name is a single contiguous alphanumeric word (like 'SalesData'), the INDIRECT function can successfully parse it without the single quotation marks.
Can I use INDIRECT to reference closed workbooks?
No, the INDIRECT function only works with open workbooks. If the referenced workbook is closed, the formula will immediately return a #REF! error regardless of whether your quotation mark syntax is correct.
What if my sheet name doesn't have spaces but I still get a #REF! error?
If there are no spaces, ensure the target worksheet actually exists and the name is spelled exactly as it appears on the sheet tab. Also, verify that the workbook you are referencing is currently open and that the cell reference being built resolves to a valid Excel range.
Does WPS Spreadsheet support the INDIRECT function exactly like Excel?
Yes, WPS Spreadsheet has complete formula parity for INDIRECT, including the requirement for single quotes when handling sheet names with spaces. Formulas created in Excel will work identically in WPS Spreadsheet.
