Function Problems

How to Reference a Specific Worksheet in Another Excel Workbook

Huma Ashraf Ch Sep 27, 2026 869 views

Question details

The user needs to retrieve data, such as payroll records, from a specific tab in an external Excel workbook dynamically using a variable date or worksheet name.

How to Reference a Specific Worksheet in Another Excel Workbook

Product: Excel
Device & OS: not provided
Scenario: Pulling data dynamically from an external workbook based on variable worksheet names without manually hardcoding the target references.
Observed behavior: Needs to build a dynamic reference to another workbook but faces challenges with proper syntax and connection reliability when the source file is closed.

Before you start

Ensure that the external source workbook is saved locally on your computer and that both workbooks are open to prevent calculation errors when using dynamic references.

Solution 1Recommended

Use the INDIRECT Function for Dynamic References

The INDIRECT function allows you to construct a worksheet reference dynamically by combining text strings and cell values.

This method is ideal when you have a cell containing the exact name of the target worksheet. By using INDIRECT, Excel translates the text string into a valid cell reference.

Keep in mind that both workbooks must be open simultaneously for this formula to evaluate correctly.

Open both workbooks

Launch Excel and open both your destination workbook and the source workbook (e.g., BOOK1.xlsx).

Select the target cell

Click on the cell in your destination workbook where you want the external data to be displayed.

Enter the INDIRECT formula

Type the formula: =INDIRECT("'[BOOK1.xlsx]"&A1&"'!A1") where BOOK1.xlsx is your source file, the first A1 contains the sheet name you want to target, and the second A1 is the cell to extract.

Press Enter to calculate

Hit Enter. The cell will now dynamically display the data from the referenced external worksheet.

Use the INDIRECT Function for Dynamic References

Keep External Files Open: If the source workbook is closed, the INDIRECT function cannot resolve the reference and will result in a #REF! error.

Solution 2

Use Power Query for Closed Workbooks

Power Query provides a more reliable method for pulling data from external workbooks without needing them to be open.

Try WPS Spreadsheet

Dynamically Reference Worksheets with WPS Spreadsheets

WPS Spreadsheets provides robust native support for advanced data retrieval functions like INDIRECT, allowing you to easily build dynamic references across multiple workbooks.

1. Open your workbooks: Launch WPS Spreadsheets and open both your destination workbook and the source workbook.
2. Select the formula cell: Click on the specific cell where you want to retrieve and display the external data.
3. Apply the INDIRECT function: Type the formula =INDIRECT("'[SourceBook.xlsx]"&A1&"'!B2") into the formula bar, modifying the file and cell names to match your specific data structure.
4. Execute the formula: Press Enter to instantly fetch the data. Keep both files open using WPS Spreadsheets' tabbed interface for seamless live calculation.

Native support for INDIRECT, VLOOKUP, and advanced referencing functionsSeamless format compatibility with Microsoft Excel (.xlsx) filesLightweight, fast, and highly efficient memory managementBuilt-in tabbed viewing interface to easily manage multiple open workbooks

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when linking workbooks?

This is a known limitation of the Excel INDIRECT function. It requires the referenced external workbook to be currently open in the background. If the source file is closed, the formula cannot evaluate the text string into a valid memory reference and returns a #REF! error.

Can I combine INDIRECT with VLOOKUP to search another workbook?

Yes, you can nest the INDIRECT function inside the table_array argument of a VLOOKUP function. This allows you to dynamically change which external worksheet VLOOKUP searches based on a cell value, provided that the external workbook remains open.

Is there a way to reference a closed workbook dynamically?

Because the INDIRECT function does not work with closed workbooks, the best alternative is to use Power Query. By setting up a data connection to the external file, you can import and refresh data seamlessly without needing to keep the source workbook open.

Excel & Data Problems