Change an External Workbook Name in Excel Formulas Using a Cell
Question details
Use a cell value to change the external workbook referenced by Excel formulas.

- 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.
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.
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.
In cell K1, enter the full source filename, such as Sales.xlsx, and open that workbook in Excel.
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.
To reference a different workbook, open it and change K1 to its full filename. The new workbook must contain the specified sheet and range.

Replace the Workbook Name in Existing Excel Formulas
Use Find and Replace for a one-time change when a cell-driven reference is not necessary.
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.

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.




