Function Problems

Change an External Workbook Name in Excel Formulas Using a Cell

Aamir Naveed AkramAamir Naveed Akram Nov 14, 2026 868 views

Question details

Use a cell value to change the external workbook referenced by Excel formulas.

Change an External Workbook Name in Excel Formulas Using a Cell
Product
Microsoft Excel
Device & OS
not provided
Scenario
A formula uses INDEX to retrieve data from another workbook, and the user wants to change the source workbook name without editing each formula.
Observed behavior
A normal external reference does not interpret a cell value as the workbook name. INDIRECT can build a reference from text, but the source workbook must be open.
Before you start

Identify the source workbook name, sheet name, and range used by your formulas. For a dynamic INDIRECT reference, open the source workbook and enter its full filename, including the extension, in a cell.

Solution 1Recommended

Build a Dynamic External Reference With INDIRECT

Use this approach when the workbook name must change based on a cell value and the source workbook can remain open.

INDIRECT converts a text string into a reference that INDEX can use. Standard Excel INDIRECT references to another workbook return an error if that workbook is closed.

1
Enter the workbook name

In cell K1, enter the full source filename, such as Sales.xlsx, and open that workbook in Excel.

2
Create the formula

In the destination workbook, enter =INDEX(INDIRECT("'["&$K$1&"]Sheet1'!$A$1:$A$100"),1). Replace Sheet1 and the range with the actual source sheet and range.

3
Change the source

To reference a different workbook, open it and change K1 to its full filename. The new workbook must contain the specified sheet and range.

Build a Dynamic External Reference With INDIRECT
Closed workbooks are not supported: If the source workbook is closed, INDIRECT returns a #REF! error. For data that must be read from closed files, consider importing it with Power Query instead.
Free Microsoft Office alternative

Work With Spreadsheet Formulas in WPS Office

WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open Excel files and migrate everyday spreadsheet work without changing your file format. WPS does not change how external references work in Microsoft Excel.

Find and replace text in spreadsheet formulasCompatible with Microsoft Excel XLSX and XLS filesFamiliar spreadsheet interface for an easier transitionFree and lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can I put a cell reference directly in an external INDEX formula?

A normal external reference cannot substitute a cell value for the workbook name. Build the reference as text with INDIRECT, then pass it to INDEX.

Why does INDIRECT return #REF! for my external workbook?

The source workbook may be closed, or the filename, sheet name, or range may be incorrect. Open the source workbook and check the text assembled by the formula.

How do I update many external workbook names at once?

Select the affected formulas and press Ctrl+H. Replace the old bracketed workbook filename with the new one, then check the updated formulas and results.

Can I retrieve data from a closed workbook without INDIRECT?

Yes. In Excel, use Data > Get Data > From File > From Workbook to import data with Power Query. You can refresh the query when the source data changes.