Use a Cell to Define a Closed Workbook File Name in Excel
Question details
The user needs to use a cell value to select an Excel source workbook by file name, including when the source workbook is closed.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A model number in a cell determines which workbook should supply data to an Excel file.
- Observed behavior
- A normal external reference cannot insert a cell value into its file name, and INDIRECT returns an error when the source workbook is closed.
Confirm the source folder, workbook extension, and source sheet or named range. For the Power Query method, check that your Excel version supports Power Query and that you can access the source file.
Use Power Query to Select a Closed Workbook
Use this method when the workbook name must come from a cell and the source workbook may be closed.
This example imports the Data sheet from a workbook whose name is stored in an Excel table. Change the folder and sheet name to match your files.
Enter Model in A1 and the workbook name without .xlsx, such as MOD1220014, in A2. Select A1:A2, choose Insert > Table, and confirm that the table has headers.
Select the table and use Table Design > Table Name to name it ModelInput.
Choose Data > Get Data > From Other Sources > Blank Query, then open Home > Advanced Editor.
Replace the editor contents with this example, changing the folder path as needed: let Model = Text.From(Excel.CurrentWorkbook(){[Name="ModelInput"]}[Content]{0}[Model]), Source = Excel.Workbook(File.Contents("C:\Reports\" & Model & ".xlsx"), null, true), DataSheet = Source{[Item="Data", Kind="Sheet"]}[Data] in DataSheet. Select Done.
Choose Home > Close & Load. After changing the model value in the table, use Data > Refresh All to import data from the newly selected workbook.

Use INDIRECT When the Source Workbook Is Open
Use a formula built from the model number when you can keep the source workbook open in Excel.
Create a Fixed External Link to a Workbook
Use a direct external link if the source workbook name does not need to change based on a cell.
Work With Excel Files in WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open existing Excel files and continue editing with a straightforward transition from Microsoft Office. It does not fix Excel's closed-workbook INDIRECT limitation.

Frequently Asked Questions
Why does INDIRECT return #REF! when the source workbook is closed?
Excel's INDIRECT function requires an external workbook to be open to resolve the text reference. Open the source workbook or use Power Query to read the closed file.
Will Power Query switch files automatically when I change the model number?
The query reads the updated table value when it refreshes. After changing the model number, select Data > Refresh All to load the newly selected file.
Can I use a normal external formula with a file name stored in a cell?
A normal external reference uses a fixed workbook name; it cannot substitute a cell value into that part of the reference. Use Power Query for a closed workbook or INDIRECT for an open one.
What should I check if the Power Query import fails?
Check the folder path, model value, .xlsx extension, and Data sheet name in the query. Also confirm that the selected file exists and that you have permission to read it.




