Fix INDIRECT and VLOOKUP with a Dynamic External Excel Range in WPS Spreadsheets
Question details
The user needs to correct a #VALUE! error when using INDIRECT to build a changing VLOOKUP range that points to a different workbook and worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating a dynamic VLOOKUP range with INDIRECT across an external workbook and worksheet.
- Observed behavior
- The formula returns #VALUE! because the INDIRECT text reference does not include the external workbook and worksheet qualifier.
Open the workbook that contains the formula and verify that the external workbook name, worksheet name, and cell range are typed exactly. Keep the external workbook accessible from WPS Spreadsheets before testing the formula.
Rewrite the INDIRECT Reference with the External Workbook and Worksheet Prefix
Use this when you want to keep INDIRECT and need the text string to build a fully qualified external range for VLOOKUP.
INDIRECT returns a reference from a text string. If the string only contains the local range or a partial address, WPS Spreadsheets cannot resolve it to an external worksheet and may return #VALUE!.
Click the cell that shows the #VALUE! error, then click the formula bar or press F2 to edit the formula.
Rewrite the range inside INDIRECT as =INDIRECT("'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:$H$"&AMs) so it includes the workbook, worksheet, and dynamic row variable.
Use the INDIRECT result as the table_array argument, for example =VLOOKUP(A2,INDIRECT("'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:$H$"&AMs),2,FALSE), and press Enter.
Use INDEX to Build a Dynamic External Range Without INDIRECT
Use this for a more reliable formula when the external workbook may be closed or when you want to avoid INDIRECT volatility.
Fix Dynamic External Range Formulas in WPS Spreadsheets
Use WPS Spreadsheets to edit the INDIRECT or INDEX formula with the full external workbook and worksheet qualifier, so VLOOKUP reads changing Excel ranges without #VALUE! errors.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets, click Menu > Open, and select the workbook that contains the formula.
- 2. Edit the formula cell: Select the cell showing the #VALUE! error and click the formula bar or press F2.
- 3. Add the external workbook and worksheet prefix: Change the INDIRECT text to include '[Workbook.xlsx]Sheet1'! before the range, or switch to an INDEX-based external range.
- 4. Confirm the lookup result: Press Enter and check that VLOOKUP returns the expected value from the external range.
Frequently Asked Questions
Why does INDIRECT return #VALUE! with an external workbook?
Usually the text string inside INDIRECT is missing the external workbook and worksheet qualifier. Select the formula cell, press F2, and check that the text starts with '[Workbook.xlsx]Sheet1'! before the range.
Does INDIRECT work when the external workbook is closed?
INDIRECT to another workbook typically requires the source workbook to be open. If the workbook is closed, open it first or use an INDEX-based external range instead.
Can I use INDEX instead of INDIRECT for a dynamic VLOOKUP range?
Yes. Replace INDIRECT with a formula such as =VLOOKUP(A2,'[Book.xlsx]Sheet1'!$C$2:INDEX('[Book.xlsx]Sheet1'!$H:$H,AMs),2,FALSE).
What should the AMs cell contain in this formula?
AMs should contain a row number that defines the end of the range. Select AMs and confirm it contains a number or a formula that returns a number, not text.
