Exceljet

Quick, clean, and to the point

Increment cell reference with INDIRECT

Excel formula: Increment cell reference with INDIRECT
Generic formula 
=INDIRECT(sheet&"!"&CELL("address",A1))
Explanation 

To increment a reference created as text inside the INDIRECT function, you can use the CELL function. In the example shown, the formula in D5 is:

=INDIRECT($B$5&"!"&CELL("address",A1))

Which increments as the formula is copied down.

How this formula works

Consider a simple dynamic reference to Sheet2 using the INDIRECT in a formula like this:

=INDIRECT($B$5&"!"&"A1"))

If we change the sheet name in B5 to another (valid) name, INDIRECT will return a reference to A1 in the new sheet.

However, if we copy this formula down the column, the reference to A1 won't change, because "A1" is hardcoded as text.

To solve this problem, we use the CELL function to generate a text reference from a regular cell reference:

CELL("address",A1)

With "address" as the first argument, and A1 as the second argument, the CELL function returns a string like "$A$1". Because A1 is a regular cell reference, it will increment normally as the formula is copied down the column. The result in D5:D9 is a series of formulas like this:

=INDIRECT("Sheet2!$A$1")
=INDIRECT("Sheet2!$A$2")
=INDIRECT("Sheet2!$A$3")
=INDIRECT("Sheet2!$A$4")
=INDIRECT("Sheet2!$A$5")

In each case, INDIRECT resolves each text string to a reference and Excel returns the value at the given cell in Sheet2.

Note: both INDIRECT and CELL are volatile functions and recalculate with every worksheet change. This can cause performance problems in more complex worksheets.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.