Hi ,
When you use INDIRECT , the parameter to it is supposed to be a string.
Let us consider a few examples , starting with the following :
Cell A1 contains the text string $B$1 , which incidentally is a valid address.
Cell B1 contains the number 37
We create a named range TempA , which refers to the cell A1.
We create a named range TempS , which contains the text string "$A$1"
...................................................................................................................
Now , let us see the results of various formulae :
1. =INDIRECT($A$1) gives 37 ; we can reason that cell A1 contains the string $B$1 , and so the INDIRECT function uses the string found in A1 as an address , and returns the contents of that address i.e. 37.
Using this same argument , then :
2. =INDIRECT("$B$1") uses the string $B$1 as an address , and returns the contents of that address i.e. 37.
Continuing the argument along the same lines :
3. =INDIRECT(TempA) uses the contents of TempA , which is the string $B$1 ( since TempA refers to $A$1 ) , as an address , and returns the contents of that address i.e. 37.
So far the reasoning appears to be consistent.
What will the following formula return ?
4. =INDIRECT("$A$1")
Here , the INDIRECT function uses $A$1 as an address , and returns the contents of that address i.e. the text string $B$1.
What about this ?
5. =INDIRECT("TempA")
Here , the INDIRECT function uses TempA as an address , and returns the contents of that address i.e. the text string $B$1 , since TempA refers to A1.
What about this ?
6. =INDIRECT(TempS)
Here , the INDIRECT function uses TempS as a valid address string , and returns the contents of that address i.e. the text string $B$1 , since TempS refers to a text string $A$1.
The reasoning is still consistent.
Now , let us see what happens in each of the above cases when A1 contains a range instead of a single cell.
Change A1 to the following text string : $B$1:$B$2
The results of the above 6 formulae are now :
1. ={37;0} , which however is displayed as a #VALUE! error. If you enter the formula as an array formula , using CTRL SHIFT ENTER , it does display 37 , but the point is that the real result is an array {37;0}.
2. =INDIRECT("$B$1:$B$2") does the same as the first one returning a #VALUE! error. If you enter the formula as an array formula , using CTRL SHIFT ENTER , it does display 37 , but the point is that the real result is an array {37;0}.
3. This behaves the same way as the first two above.
4. This does not change its behaviour in any way ; earlier , when A1 contained the address of one cell , it returned that ; now that A1 contains the address of the range $B$1:$B$2 , it returns that.
5. This also returns the range address $B$1:$B$2.
6. This also returns the range address $B$1:$B$2.
Now , we come to the last step of testing ; suppose we wrap a SUM function around each of the above formula , what do we expect to happen ?
Expectedly , 1 , 2 and 3 return the value 37 , since the SUM operates on the array , and returns the value of 37 + 0 , which is 37.
Surprisingly , 4 , 5 and 6 return 0 , since SUM operating on a text string returns a zero value.
With the above background , we can easily say why INDIRECT when used with OFFSET gives an error result ; the answer is because the OFFSET function does not return an address , it returns the contents of that address , which is a value.
When we define a named range Temp as referring to :
=OFFSET(Sheet1!$B$1,,,3,1)
what we are saying is that Temp is to refer to the range B1:B3.
Now , if we use the formula :
=SUM(Temp)
we will get the sum of the values in the range B1:B3.
When we try the formula :
=SUM(INDIRECT("Temp"))
we are telling the INDIRECT function to use the string in Temp as an address ; this would work if Temp did contain the string "$B$1:$B$3" ; but Temp is actually pointing to the range B1:B3 ; since this is not a string , INDIRECT cannot make use of it.
Narayan
