Function Problems

How to Use INDIRECT with a Dynamic Excel Sheet Name

Partner Editor Oct 1, 2026 869 views

Question details

The user needs to dynamically reference a worksheet name within a formula using the INDIRECT function, rather than hardcoding the sheet name text.

How to Use INDIRECT with a Dynamic Excel Sheet Name

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Building dynamic cross-sheet formulas that pull data from different worksheets based on variable cell values or sheet index numbers.
Observed behavior: The native SHEET function returns an index number rather than the text string of the sheet name, which causes INDIRECT to fail unless a proper text string like 'Sheet2'!E3 is constructed.

Before you start

Ensure that the target worksheets already exist in your workbook and note whether their names contain spaces, as spaces require special single quotation marks when constructing the INDIRECT reference.

Solution 1Recommended

Use a Cell Value for the Dynamic Sheet Name

The most straightforward way to dynamically reference a sheet name is by typing the target sheet name into a cell and concatenating it within the INDIRECT function.

This method avoids complex macros and works universally across all versions of Excel and WPS Spreadsheet. You simply reference a cell that contains the name of the worksheet you want to extract data from.

Define the target sheet name

Select a cell in your current worksheet (e.g., A1) and type the exact name of your target worksheet, such as 'Sales Data'.

Write the INDIRECT formula

Click on the cell where you want the result to appear and type the formula: =INDIRECT("'" & A1 & "'!E3"). This specific example references cell E3 on the target sheet.

Execute the formula

Press Enter to execute. The formula will automatically update and pull data from a different sheet if you change the text in cell A1.

Use a Cell Value for the Dynamic Sheet Name

Crucial Syntax for Spaces: The single quotes (' ') inside the double quotes are crucial if your sheet name contains spaces. The construction "'" & A1 & "'!E3" ensures Excel correctly formats the reference as 'Sales Data'!E3.

Solution 2

Extract Sheet Names Using a Named Range and GET.WORKBOOK

For workbooks where you want to reference a sheet by its index number, use an Excel 4.0 macro function to list all sheet names dynamically.

Powerful Spreadsheet Alternative

Easily Manage Dynamic Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like INDIRECT, INDEX, and dynamic references, allowing you to build complex data models effortlessly and at no cost.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Input reference data: Input your target sheet name into a reference cell, such as A1.
3. Enter the formula: Select the destination cell and input the standard =INDIRECT("'"&A1&"'!Cell") syntax.
4. Fetch cross-sheet data: Press Enter to instantly fetch and link your dynamic cross-sheet data.

Fully compatible with Microsoft Excel formulas, functions, and named ranges.Intuitive formula builder and built-in error checking tools to easily spot syntax issues.Free and lightweight software for processing cross-sheet references and large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

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

A #REF! error typically occurs if the referenced worksheet name is misspelled, the sheet doesn't exist, or if the sheet name contains spaces but the single quotes (' ') were omitted in your INDIRECT formula string.

Can I use the SHEET() function directly inside INDIRECT?

No, the SHEET() function returns the numerical index of a worksheet (e.g., 2), while INDIRECT strictly requires the exact text name of the worksheet (e.g., 'Sheet2'). You must map the index number to a sheet name first, usually via a named range or helper table.

Does the INDIRECT function work with closed workbooks?

No, the INDIRECT function only evaluates references to external workbooks if those target workbooks are currently open. If the external workbook is closed, the INDIRECT formula will instantly return a #REF! error.

Excel & Data Problems