Data Import & Export

Retrieve Data from a Closed Excel Workbook with a Dynamic File Name

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Retrieve data from a closed Excel workbook when the source file name may change.

Retrieve Data from a Closed Excel Workbook with a Dynamic File Name
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the source path

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.

2
Create a blank query

Go to Data > Get Data > From Other Sources > Blank Query. In Power Query Editor, select Home > Advanced Editor.

3
Add the query

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.

4
Load and refresh the data

Select Home > Close & Load. When the source file changes, update the SourcePath cell and select Data > Refresh All to load the new file.

Use Power Query to load a workbook path from a cell
Keep the source structure consistent: The path must point to an accessible workbook containing the specified sheet. If the sheet name or structure changes, edit the query before refreshing.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLS and XLSX filesWork with spreadsheet data and lookup formulasUse a familiar interface for everyday spreadsheet tasksMove existing Office documents into a lightweight, free suite
QA img-9

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.