Hello Excel Gurus,
I am having trouble understanding an excel formula I have never come across, and was wondering if y'all would generously help walk me through the meaning.
=IFERROR(INDIRECT($A10&"!dc$"&S$1),"")
Hello Excel Gurus,
I am having trouble understanding an excel formula I have never come across, and was wondering if y'all would generously help walk me through the meaning.
=IFERROR(INDIRECT($A10&"!dc$"&S$1),"")
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, "")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks