How to Use INDIRECT with Excel File Names Containing Spaces
Question details
The INDIRECT and XLOOKUP formulas return errors when referencing an external Excel workbook that has spaces in its file name.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dynamically referencing external workbooks with spaces in their file names using the INDIRECT and XLOOKUP functions.
- Observed behavior
- The formula fails and returns an error because of missing or incorrectly placed apostrophes around the workbook and worksheet string references.
Ensure that the external Excel workbook you are referencing is currently open on your computer, as the INDIRECT function requires external files to be open to evaluate successfully without returning a #REF! error.
Properly Enclose the Workbook and Sheet Reference with Apostrophes
Fix the reference error by correctly placing apostrophes around the workbook and worksheet string inside your INDIRECT formula.
When an external workbook or worksheet name contains spaces, Excel requires the entire path to be enclosed in single quotation marks (apostrophes). If these are omitted or placed incorrectly within the INDIRECT string concatenation, Excel cannot parse the file path, causing the formula to fail.
Determine the exact name of your external workbook (e.g., test1 - Copy.xlsx) and the target worksheet.
Build your INDIRECT text string starting with a double quote, followed by a single quote and an opening square bracket: "'[
Add the workbook name, a closing square bracket, and a single quote immediately before the exclamation mark. For example: "'[test1 - Copy.xlsx]Sheet1'!"
Combine the static strings with your dynamic cell references using ampersands (&). A complete XLOOKUP and INDIRECT formula should look like: =XLOOKUP($A5,INDIRECT("'[test1 - Copy.xlsx]"&$A$3&"'!$A:$A"),INDIRECT("'[test1 - Copy.xlsx]"&$A$3&"'!"&C$1))
Press Enter to apply the formula. Make sure the external workbook is open in the background to allow INDIRECT to evaluate the reference and retrieve your data.
Use Advanced Formulas and Link External Workbooks Seamlessly in WPS Office
WPS Spreadsheet offers full compatibility with standard Excel functions, including INDIRECT and XLOOKUP. You can easily reference external workbooks and manage complex data lookups within a lightweight, highly compatible interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and open both your main working spreadsheet and the external workbook you intend to reference.
- 2. Enter the formula: Select the cell where you want to perform the data lookup and start typing =INDIRECT(.
- 3. Add required syntax: Construct your string reference, ensuring you enclose the external workbook name in single quotes if it contains spaces (e.g., INDIRECT("'[Data File.xlsx]Sheet1'!A:A")).
- 4. Calculate and evaluate: Press Enter. WPS Spreadsheet will instantly calculate the formula and retrieve the correct data across the linked workbooks.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error with external links?
This typically happens for two reasons: the external workbook you are referencing is currently closed, or the syntax for the workbook name containing spaces is missing the required single quotation marks.
Can I use INDIRECT to reference a closed workbook?
No, the INDIRECT function only works with open external workbooks. If you need to pull data from a closed workbook, you must use direct cell links or alternative data extraction methods like Power Query.
Do I need single quotes around file names without spaces?
While not strictly required for file names without spaces, it is considered best practice to always include single quotes around external workbook and worksheet references. This prevents unexpected errors if the file or tab name is later changed to include a space.
