How to Reference a Workbook Named in an Excel Cell with INDIRECT in WPS Office
Question details
The user needs to create a formula that dynamically references an external workbook based on a workbook name entered in cell A2. The referenced workbook must remain open because INDIRECT cannot normally retrieve values from a closed external workbook.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants to build a dynamic external workbook reference from a workbook name stored in cell A2.
- Observed behavior
- INDIRECT cannot normally retrieve values from a closed external workbook; the referenced workbook must be open for the formula to return a value.
Open both the workbook containing the formula and the external workbook you want to reference. Confirm that cell A2 contains only the workbook name and that the target worksheet name and cell address are correct.
Use INDIRECT to Build a Dynamic External Workbook Reference from Cell A2
Use this method when you need to dynamically refer to an external workbook whose name is stored in cell A2, and the external workbook can remain open while the formula calculates.
The INDIRECT function converts a text string into a valid reference. When the string is built from a cell value, you can change the referenced workbook without rewriting the formula. However, INDIRECT only evaluates external references while the source workbook is open in WPS Office.
In WPS Office Spreadsheets, open the workbook you want to reference. Click the worksheet tab at the bottom to confirm its exact name, such as Sheet1, because the formula must match it exactly.
Go to the workbook that contains the formula, select cell A2, and type the external workbook name without the .xls extension, for example Budget2025.
Select the cell where you want the result, type =INDIRECT("["&A2&".xls]Sheet1!B6"), and press Enter. Replace Sheet1 with the actual worksheet name and B6 with the cell you need.
Confirm the formula returns the expected value. Keep the external workbook open; closing it will make INDIRECT return a #REF! error.
Build a Dynamic Workbook Reference in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create an INDIRECT formula that points to a workbook name stored in a cell. Open both workbooks, enter the workbook name, and WPS Office resolves the reference with Excel-compatible syntax.
- 1. Open the source and external workbooks: Launch WPS Office Spreadsheets and open both the workbook containing the formula and the workbook you want to reference.
- 2. Enter the workbook name in cell A2: In the formula workbook, click cell A2 and type the external workbook name without its file extension.
- 3. Create the INDIRECT formula: Click the cell where the result should appear, enter =INDIRECT("["&A2&".xls]Sheet1!B6"), and adjust the sheet name and cell reference to match your file.
- 4. Keep the external workbook open: Leave the referenced workbook open in WPS Office to allow INDIRECT to resolve the value and update the formula result.
Frequently Asked Questions
Why does my INDIRECT formula show #REF! when I reference another workbook?
This usually means the external workbook is closed, the workbook name in A2 is spelled differently, or the sheet name in the formula does not match the actual sheet name. Open the external workbook in WPS Office and check all three details.
Can INDIRECT pull data from a closed workbook in WPS Office?
No. INDIRECT cannot normally evaluate an external reference from a closed workbook. The referenced workbook must stay open while the formula calculates.
Should I enter the .xls or .xlsx extension in cell A2 for this INDIRECT formula?
If you use the accepted formula =INDIRECT("["&A2&".xls]Sheet1!B6"), cell A2 should contain only the workbook name, such as Budget2025. Change .xls to .xlsx in the formula if your file uses that extension.
Can this dynamic reference work between WPS Office and Microsoft Excel files?
Yes. WPS Office Spreadsheets can open Excel workbook formats and uses the same INDIRECT syntax, so the formula works when both files are open in WPS Office.
What should I do if the worksheet name contains spaces?
If the sheet name has spaces, include it inside apostrophes in the formula, for example =INDIRECT("["&A2&".xls]'Sales 2024'!B6"). The apostrophes help INDIRECT read the sheet name correctly.
