Retrieve Data from a Closed Excel Workbook with a Dynamic File Name
Question details
Retrieve data from a closed Excel workbook when the source file name may change.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell identifies an external workbook, and the user wants to retrieve its data without opening it.
- Observed behavior
- INDIRECT cannot retrieve values from a closed external workbook, even when the reference is built from a file name stored in a cell.
Check that the source workbook is accessible and that each replacement file contains a sheet with the same name and structure. For the Power Query method, confirm that your Excel version supports creating and refreshing queries.
Use Power Query to load a workbook path from a cell
Use this method when the source file name changes and you can refresh the imported data.
Power Query reads the file path from a named cell and loads data from the closed source workbook. Changing the cell does not update the results until you refresh the query.
In the destination workbook, enter the full path and file name in a cell, such as E:\ExcelForum\postalcodes.xlsx. Select the cell, click the Name Box to the left of the formula bar, type SourcePath, and press Enter.
Go to Data > Get Data > From Other Sources > Blank Query. In Power Query Editor, select Home > Advanced Editor.
Replace the editor contents with: let Path = Excel.CurrentWorkbook(){[Name="SourcePath"]}[Content]{0}[Column1], Source = Excel.Workbook(File.Contents(Path), null, true), Data = Source{[Item="Sheet1", Kind="Sheet"]}[Data] in Data. Change Sheet1 to the source sheet name if necessary, then select Done.
Select Home > Close & Load. When the source file changes, update the SourcePath cell and select Data > Refresh All to load the new file.

Use an external lookup formula for a fixed workbook
Use a direct external reference when the source workbook name and location stay the same.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel formats, making it easier to move existing files into WPS. It does not fix Excel's INDIRECT limitation or replace the Power Query steps above.

Frequently Asked Questions
Can INDIRECT read from a closed Excel workbook?
No. INDIRECT can build an external reference from text, but the source workbook must be open for that reference to return a value.
How do I switch the source file in Power Query?
Enter the new full path in the named SourcePath cell, then select Data > Refresh All. The new workbook must contain the sheet specified in the query.
Can a formula retrieve data from a closed workbook?
Yes. A normal external reference or an INDEX and MATCH formula can reference a closed workbook when the source path and file name are fixed.
Why does the Power Query refresh fail after I change the file name?
Check that the path in SourcePath is correct and accessible and that the workbook contains the sheet named in the query. Use Data > Queries & Connections to open and edit the query if the sheet name has changed.




