Indirect Formula Explanation

The INDIRECT function will take a string, and return the range that corresponds to that string. For example, if you use

=INDIRECT("Sheet1!DC1")

it is equivalent to

=Sheet1!DC1

Suppose you know that you want to return a value in DC1, but the sheet can change. The sheet name would be in A10 and you can do this:

=INDIRECT(A10 & "!DC1")

If A10 has the string "Sheet2" then the result is equivalent to

=Sheet2!A1

Now suppose you know that you want to return a value in column DC, but the row can change. The row number is contained in S1.


=INDIRECT(A10 & "!DC" & S1)

If A10 has the string "Sheet2", and S1 has the number 12, then the result is equivalent to

=Sheet2!DC12

The added $ signs indicate that if this formula is copied to another cell, the indicator (row or column) after the $ will not change.

When you wrap ISERROR around an expression, it will return a blank if the expression evaluates to an error.

=IFERROR(expression, "")