Fix VLOOKUP and INDIRECT #REF! Error with Closed Excel Workbooks
Question details
The user needs to dynamically reference external worksheets using VLOOKUP and INDIRECT, but encounters errors when the external source file is not actively open.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using a VLOOKUP formula combined with INDIRECT to dynamically select and pull data from worksheets (like specific week numbers) located in an external workbook.
- Observed behavior
- The formula correctly retrieves data while the source external workbook is open, but instantly returns a #REF! error once the source workbook is closed.
Verify that your external file paths and worksheet names are completely accurate. Keep in mind that the INDIRECT function is a volatile function that inherently requires the target workbook to be open in the background to evaluate references.
Keep the Source Workbook Open or Consolidate Data
Since INDIRECT cannot read closed files, the most straightforward workaround is to ensure the source file remains open or to move the source data into your active workbook.
The INDIRECT function evaluates a text string as a cell reference. By design, it cannot access the memory of a closed external file.
Consolidating the dynamic sheets into your main workbook completely eliminates the need for external references, making your formulas faster and more reliable.
Before checking or calculating the VLOOKUP formulas, double-click and open the external source workbook in Excel.
Right-click the dynamically referenced tabs in the source workbook, select 'Move or Copy', and copy them directly into your main working file.
Modify your INDIRECT string to reference the local sheets instead of the external workbook path.
Use Direct External References
Standard VLOOKUP formulas can retrieve data from closed workbooks as long as the external reference is hardcoded directly rather than passed through INDIRECT.
Import Data using Power Query
Power Query can extract and combine data from closed external workbooks dynamically, replacing the need for complex INDIRECT formulas entirely.
Easily Manage Complex Formulas and External Data in WPS Spreadsheet
WPS Spreadsheet provides a powerful, user-friendly environment for managing complex datasets. Easily consolidate multiple sheets or handle direct external references smoothly without suffering from constant #REF! errors.
- 1. Open your workbooks in WPS Spreadsheet: Launch WPS Office and open both your main workbook and the external data source workbook. They will open conveniently in the same window as tabs.
- 2. Link data seamlessly: Start typing your VLOOKUP formula in the main file. When it's time to select the table array, simply click the tab of the source workbook and highlight the data range.
- 3. Consolidate if necessary: If you want to close the external file, use the 'Move or Copy Sheet' feature by right-clicking the source tab to bring the data locally.
- 4. Save in standard format: Save your document. WPS Spreadsheet fully preserves external links and complex functions in the standard .xlsx format.
Frequently Asked Questions
Why does INDIRECT return a #REF! error when the source file is closed?
The INDIRECT function evaluates text strings into active memory references. It cannot read the file directory of your computer. Therefore, the target workbook must be actively open in your spreadsheet application for the memory reference to be valid.
Can I force the INDIRECT function to work with a closed Excel workbook?
No, there is no native way to make INDIRECT read from a closed file. You must either keep the source file open, switch to direct external references, or use macros (VBA) to pull the data.
Does standard VLOOKUP work on closed workbooks?
Yes. If you use a direct, hardcoded external reference in your VLOOKUP (e.g., ='[Data.xlsx]Sheet1'!$A$1:$B$10), the formula will successfully retrieve the data even when the source file is closed.
How can I dynamically select sheet names without using INDIRECT?
To dynamically pull data from changing sheet names in closed workbooks, you can use Power Query to append all target sheets into one master table locally, or utilize VBA scripts to open the target file in the background, extract the required sheet data, and close it automatically.
