Function Problems

How to Use a Cell Value in a Dynamic Excel Reference to Another Workbook

Chanuka Geekiyanage Oct 20, 2026 868 views

Question details

The user needs to create a dynamic formula that references a cell, worksheet, or workbook using names stored in other cells.

How to Use a Cell Value in a Dynamic Excel Reference to Another Workbook

Product: Excel
Device & OS: not provided
Scenario: Pulling data dynamically from an external workbook by using a cell value to dictate the worksheet or workbook name in the formula.
Observed behavior: The INDIRECT function fails and returns an error when the external workbook name contains spaces, or when the external workbook is closed.

Before you start

Ensure that the external workbook you are trying to reference is open, as the INDIRECT function cannot resolve dynamic references to a closed workbook.

Solution 1Recommended

Use INDIRECT with Single Quotation Marks

Use the INDIRECT function wrapped with single quotation marks to safely concatenate dynamic workbook and sheet names, preventing errors caused by spaces.

To build a dynamic reference, you must concatenate the workbook name, worksheet name, and cell address. If your workbook or worksheet name contains spaces, standard references will break. Enclosing the external reference path in single quotation marks inside your INDIRECT formula ensures the text string is processed correctly.

Prepare your cell values

Ensure the cell you want to use for the dynamic reference (e.g., D1) contains the exact worksheet name you wish to pull data from.

Start the INDIRECT function

Click the destination cell where you want the data to appear and type =INDIRECT( to start the formula.

Add the single quotation marks and brackets

Type "'[Book1 Test.xlsx]" (including the double quotes, single quote, and brackets) to specify the open workbook name.

Concatenate the dynamic cell

Add an ampersand (&) followed by your cell reference (e.g., D1) and another ampersand (&) to dynamically insert the worksheet name.

Complete the cell reference

Finish the formula by typing "'!$A$1") to specify the target cell on the dynamic sheet, and press Enter. The final formula should look like: =INDIRECT("'[Book1 Test.xlsx]"&D1&"'!$A$1")

Working with Closed Workbooks: INDIRECT generally returns a #REF! error if the source workbook is closed. For referencing closed workbooks, consider using direct external references or importing the data via Power Query.

Solution 2

Standard INDIRECT Reference for Names without Spaces

A simpler INDIRECT syntax can be used if you are absolutely certain your workbook and worksheet names do not contain spaces or special characters.

Seamlessly Manage Advanced Formulas with WPS Office

WPS Spreadsheet provides powerful support for complex functions like INDIRECT, allowing you to build dynamic references and link multiple workbooks efficiently.

1. Open WPS Spreadsheet: Launch WPS Office and open the Spreadsheet module.
2. Open all required files: Open both the destination workbook and the external source workbooks you plan to reference.
3. Input the dynamic formula: Select the target cell and enter your INDIRECT formula, making sure to include single quotation marks if your file names have spaces.
4. Calculate and save: Press Enter to retrieve your dynamic data instantly, then save your linked workbooks.

Fully compatible with Microsoft Excel formulas, including INDIRECT and external referencesLightweight architecture handles multiple open workbooks smoothly without crashingFree to use with a familiar interface for quick and easy data management

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic reference formula return a #REF! error?

This error most commonly occurs because the external workbook you are referencing is currently closed. The INDIRECT function requires the referenced source workbook to be open in the background to calculate successfully. It can also happen if there is a typo in the worksheet name or missing single quotes for names with spaces.

Do I always need to use single quotation marks in my dynamic references?

Single quotation marks are strictly required only when the workbook name or worksheet name contains spaces or special characters. However, it is a widely accepted best practice to always include them in your INDIRECT formulas to prevent sudden errors if a file or sheet is renamed in the future.

Are there alternatives to using INDIRECT for closed external workbooks?

Yes. Since INDIRECT does not work with closed workbooks, you can use Power Query to securely connect to and import data from closed external files. Alternatively, standard direct references (e.g., ='C:\Path\[Book1.xlsx]Sheet1'!A1) work with closed workbooks, but they cannot be made dynamic using cell values.

Excel & Data Problems