Function Problems

How to Retrieve Data from Another Workbook Sheet Using an Excel Formula

Maira Mehtab Sep 24, 2026 874 views

Question details

The user needs to retrieve a specific cell value from a worksheet in an external workbook dynamically, using a worksheet name referenced in the current workbook.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Pulling specific data, such as an invoice amount in cell L16, from an external workbook based on a dynamic worksheet name specified in cell A5.
Observed behavior: The user requires a dynamic formula solution to reference a cell in another workbook without hardcoding the worksheet name directly into the cell reference.

Before you start

Ensure both the source workbook (e.g., the invoice file) and the destination workbook are saved and open on your device, as dynamic external references require the source file to be active.

Solution 1Recommended

Use the INDIRECT Function to Reference an External Workbook

Construct a dynamic external reference using the INDIRECT function combined with the workbook name and the cell containing the sheet name.

The INDIRECT function in spreadsheets allows you to build a dynamic reference from a text string. When referencing external workbooks, the workbook name must be enclosed in square brackets, and single quotes should surround the path to accommodate any spaces in the file or sheet names.

Please note that the source workbook must remain open for the INDIRECT formula to calculate successfully. If the external workbook is closed, the formula will return a #REF! error.

Open both spreadsheet files

Open both your destination workbook (where you are writing the formula) and the external source workbook (for example, 'BA invoices.xlsx').

Enter the INDIRECT formula

In your destination cell, enter the formula: =INDIRECT("'[BA invoices.xlsx]"&A5&"'!L16"). Replace 'BA invoices.xlsx' with your actual file name, 'A5' with the cell containing the sheet name, and 'L16' with your target cell.

Verify the worksheet name

Ensure the text stored in cell A5 exactly matches the name of the target worksheet in the external workbook, including any spaces or special characters.

Source Workbook Must Be Open: Formulas using INDIRECT to reference external workbooks only evaluate correctly when the external workbook is currently open in the application.

Solution 2

Consolidate Data for Large-Scale Queries

If you have an extremely large number of worksheets or workbooks, consider consolidating the data to avoid performance issues caused by numerous external volatile formulas.

Efficient Spreadsheet Management

Seamlessly Connect Workbooks with WPS Spreadsheet

WPS Spreadsheet provides powerful formula support, including full compatibility with the INDIRECT function, allowing you to dynamically link data across multiple workbooks. It is an excellent, lightweight alternative for handling large datasets efficiently.

1. Open WPS Spreadsheet: Launch WPS Office and open both your summary workbook and the external data workbook using the convenient tabbed interface.
2. Input the dynamic formula: Select the target cell and type =INDIRECT("'[External File.xlsx]"&A1&"'!TargetCell") to construct the reference.
3. Calculate and fetch data: Press Enter to instantly fetch the data. The formula will dynamically update whenever the reference cell containing the sheet name changes.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Lightweight software that handles multiple open workbooks smoothly without lag.Intuitive tabbed interface makes it easy to switch between your source and destination files.Free to use for everyday data analysis, reporting, and cross-workbook calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when linking to another workbook?

This usually happens because the external workbook you are referencing is closed. The INDIRECT function requires the external file to be actively open in the application to evaluate the link. It can also occur if there is a typo in the workbook name, sheet name, or cell reference.

Are single quotes necessary in the INDIRECT formula string?

Single quotes are strictly required if your external workbook name or worksheet name contains spaces. It is a best practice to always include them in your formula syntax, formatted as '[Filename.xlsx]Sheetname', to prevent unexpected reference errors.

Can I use VLOOKUP with INDIRECT to search across different workbooks?

Yes, you can nest the INDIRECT function inside VLOOKUP to dynamically determine which workbook or sheet to search. For example: =VLOOKUP(B2, INDIRECT("'["&A2&".xlsx]Sheet1'!$A$1:$D$100"), 2, FALSE). Ensure the referenced workbooks remain open.

Excel & Data Problems