Function Problems

How to Copy an INDIRECT Formula Down with Incrementing Rows in Excel

Camila Milosovich Oct 12, 2026 870 views

Question details

The user needs a method to copy an INDIRECT formula down a column so that the row number inside the text reference increments automatically.

How to Copy an INDIRECT Formula Down with Incrementing Rows

Product: Spreadsheet
Device & OS: not provided
Scenario: Dragging or copying an INDIRECT formula down a column while referencing a dynamic worksheet name.
Observed behavior: Because the cell reference inside the INDIRECT function is a static text string, the row number does not automatically increment when the formula is copied to adjacent rows.

Before you start

Ensure that the cell containing your dynamic worksheet name (e.g., B1) is locked using absolute references (like $B$1) so it does not shift when you copy the formula down.

Solution 1Recommended

Use the ROW Function inside the INDIRECT String

Concatenate the ROW() function to your text string within the INDIRECT formula. This generates a dynamic row number that updates as you drag the formula down the column.

The standard INDIRECT function evaluates text strings. To make the row number dynamic, you must break the text string before the row number and use the ampersand (&) to join it with a dynamic function like ROW().

Select the starting cell

Click on the cell where you want to enter the very first INDIRECT formula in your sequence.

Enter the base formula

Type =INDIRECT("'"&$B$1&"'!D"&ROW()). In this example, $B$1 holds the sheet name, and 'D' is the target column you want to pull data from.

Adjust the row offset if necessary

If your formula starts on row 1 but you need to pull data starting from row 3 on the target sheet, adjust the ROW function by adding an offset, such as ROW()+2.

Drag the fill handle

Hover over the bottom-right corner of the cell until your cursor becomes a crosshair, then click and drag down to apply the dynamic formula to the rows below.

Use the ROW Function inside the INDIRECT String

Formula Tip: Using the ROW() function with empty parentheses returns the row number of the cell where the formula currently resides, ensuring a perfect 1-to-1 increment as you drag it down.

Advanced Spreadsheet Functions

Build Dynamic Dashboards with WPS Spreadsheet

WPS Spreadsheet provides full support for advanced volatile and lookup functions, including INDIRECT and ROW, enabling you to build powerful, automated data models with ease.

1. Open WPS Spreadsheet: Launch WPS Office and open your data workbook.
2. Input the Dynamic Formula: Select your target cell and type =INDIRECT("'"&$B$1&"'!D"&ROW()).
3. Fill the Column: Double-click the fill handle at the bottom-right of the cell to instantly apply the incrementing formula down your dataset.

100% compatible with Microsoft Excel functions like INDIRECT, ROW, and offset operations.Intuitive formula builder with built-in error checking.Free, lightweight, and loads large datasets rapidly.Familiar user interface requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the standard INDIRECT function increment rows automatically?

The INDIRECT function takes a text string and converts it into a valid reference. Because the cell coordinate (like 'D3') is enclosed in quotation marks, it acts as static text, ignoring standard relative referencing rules when copied.

How do I increment columns instead of rows with INDIRECT?

To increment columns as you drag a formula across, you can use the COLUMN() function in conjunction with the ADDRESS() function, or map the numerical column value to a letter using functions like CHAR().

Will using the INDIRECT function slow down my spreadsheet?

Yes, INDIRECT is known as a volatile function. This means it forces a recalculation every time any change is made anywhere on the worksheet, which can reduce performance if used in thousands of cells simultaneously.

What if my target worksheet name contains spaces?

If your worksheet name contains spaces, Excel requires it to be wrapped in single quotes. The formula syntax "'"&$B$1&"'!" safely wraps the dynamic sheet name inside B1 with the required single quotes.

Excel & Data Problems