Function Problems

How to Use INDIRECT with Dynamic Worksheet Names and Row References in Excel

Steve K Oct 21, 2026 868 views

Question details

The user needs an Excel formula that dynamically references a worksheet name from a specific cell and automatically updates the row reference as the formula is copied down a column.

How to Use INDIRECT with Dynamic Worksheet Names and Row References in Excel

Product: Excel
Device & OS: not provided
Scenario: Building scalable, dynamic formulas across different worksheets to avoid hardcoding sheet names and cell references.
Observed behavior: The user requires a dynamic row-adjusting formula. Additionally, the user notes that calculated values from INDIRECT formulas sometimes fail to appear when the workbook first opens until manual edits trigger a refresh.

Before you start

Ensure your target worksheets actually exist and that their names perfectly match the text entered in your reference cell. Also, check that your workbook's calculation options are set to 'Automatic' so dynamic functions update properly.

Solution 1Recommended

Build the INDIRECT Formula with the ROW() Function

Use a combination of INDIRECT, string concatenation, and the ROW() function to dynamically update row references as you drag the formula down.

The INDIRECT function turns a text string into a valid cell reference. By using the ROW() function inside the text string, you can force the formula to reference the corresponding row number dynamically without manually typing it for every cell.

Select the destination cell

Click the cell where you want to output the data (for example, in row 8).

Enter the dynamic INDIRECT formula

Type the formula =INDIRECT("'"&$B$1&"'!B"&ROW()). In this formula, $B$1 contains your target sheet name, and 'B' is the target column on that sheet.

Apply the formula to other rows

Press Enter to evaluate the formula. Click the cell again, hover over the bottom-right corner to see the fill handle, and drag it down to apply the formula to subsequent rows.

Build the INDIRECT Formula with the ROW() Function

Locking Reference Cells: Remember to make fixed references absolute by adding dollar signs (like $B$1). This ensures the sheet name reference doesn't shift when you copy the formula down.

Solution 2

Adjust Row References with an Offset

If your destination row number does not match the source row number exactly, apply a mathematical offset to the ROW() function.

Solution 3

Fix INDIRECT Calculation Issues on File Open

INDIRECT is a volatile function that may not recalculate immediately upon opening the workbook if the calculation settings are restricted.

Advanced Spreadsheet Capabilities

Create Dynamic Formulas Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports the INDIRECT function and advanced dynamic formula building. Use it to seamlessly reference multiple worksheets and build automated, scalable reports without calculation lag.

1. Open your workbook: Launch WPS Spreadsheet and open your multi-sheet workbook.
2. Input the dynamic formula: Select your target cell and type your =INDIRECT() formula using the ROW() function.
3. Copy down the column: Double-click or drag the fill handle in the bottom right corner of the cell to populate the rest of the column dynamically.
4. Verify automatic calculation: Go to the Formulas tab and ensure 'Calculate Automatically' is enabled for seamless updates.

Fully compatible with Microsoft Excel formulas, functions, and formatsHigh-performance calculation engine for handling volatile functions like INDIRECTIntuitive formula builder with automatic syntax and error checkingFree and lightweight office suite for daily productivity

Frequently Asked Questions

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

A #REF! error typically occurs if the worksheet name referenced in the cell contains a typo, the sheet doesn't exist, or an external workbook is closed. Ensure you wrap the sheet name in single quotes within your formula if the sheet name contains spaces.

How do I handle worksheet names with spaces in INDIRECT?

You must wrap the dynamic sheet name in single quotation marks within your text string. Use a concatenation structure like "'" & $B$1 & "'!" to ensure Excel parses the spaces in the sheet name correctly.

Is INDIRECT a volatile function?

Yes, INDIRECT is volatile. This means it recalculates every time any change is made to the worksheet. In massive workbooks with thousands of INDIRECT formulas, this can significantly slow down performance.

Why aren't my INDIRECT formulas updating when I open the file?

This usually happens if the workbook is set to Manual calculation or if the referenced data is in a closed external file. Check your Calculation Options under the Formulas tab and ensure it is set to Automatic, or press F9 to force a recalculation.

Excel & Data Problems