Data Import & Export

Use a Cell to Define a Closed Workbook File Name in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

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.

Use a Cell to Define a Closed Workbook File Name in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the input table

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.

2
Name the table

Select the table and use Table Design > Table Name to name it ModelInput.

3
Create a blank query

Choose Data > Get Data > From Other Sources > Blank Query, then open Home > Advanced Editor.

4
Enter the query

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.

5
Load and refresh

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 Power Query to Select a Closed Workbook
Match the source structure: The example loads the Data sheet as a table; it does not retrieve Main.InnerD.2 specifically. Adapt the query if you need a particular cell or named range, and ensure each selected workbook contains the expected sheet.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX filesWork with spreadsheet formulas and cell-based dataUse a familiar interface for everyday spreadsheet tasksMove existing Office files into a lightweight, free suite
microsoft office alternative - wps office

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.