• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect using Dynamic Named Range

polarisking

Member
Besides the chance that I'm doing something wrong, is there a structural reason why INDIRECT doesn't work when the Named Range is defined using Offset?

Named Range CHANDOO = offset('Sheet1'!A1,0,0,3,1)

Cells A1 though A3 contain 1, 2, 3 respectively.

=SUM(Chandoo) = 6
=SUM(INDIRECT("Chan"&"doo") fails on a #REF error.
=SUM(INDIRECT("Chandoo") fails on a #REF error.
 

NARAYANK991

Excel Ninja
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
 

SirJB7

Excel Rōnin

polarisking

Member
Narayan and SirJB7, thank you both. I'm very interested in WHY the INDIRECT fails when referring to a dynamic named ranges. Perhaps it has something to with the fact that multiple volatile functions are in play. The macro from SirJB7 fixed my issue.
 

NARAYANK991

Excel Ninja
Hi ,

I am willing to be corrected , but I fail to understand how your definition of a range as :

OFFSET('Sheet1'!A1,0,0,3,1)

makes it dynamic.

The issue here , in my opinion , is that INDIRECT works if its parameter is a string which translates to a valid Excel address string ; the OFFSET function returns a range , and not an address string.

Narayan
 

polarisking

Member
Narayan, I appreciate your patience. I'm not giving you enough information, so here it is in the attached file.

The range A1:A4 contains "Values", 1, 2, 3 respectively.
The rand B1: B4 contains "Sum", =Sum(Test_Hard), =Sum(Test_Offset_Hard), and =Sum(Test_Offset_Dynamic) respectively.

I have defined 3 named ranges that all appear to define the range A2:A4

Test_Hard: =Sheet1!$A$2:$A$4
Test_Offset_Hard: =OFFSET(Sheet1!$A$2,0,0,3,1)
Test_Offset_Dynamic: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Range C2:C4 contains the following formulae:
C2: =SUM(INDIRECT($E$1&"_"&$E2))
C3: =SUM(INDIRECT($E$1&"_"&$E3))
C4: =SUM(INDIRECT($E$1&"_"&$E4))

Range E1:E4 contains:
E1: "Test"
E2: "Hard"
E3: "Offset_Hard"
E4: "Offset_Dynamic"

For the values in Column C, I'm using the concatenated values in Column E (plus an underscore) to create the Named Range literals.

Column 6 is returning a value of 6 for C2, #REF! for each of C3 and C4.
 

Attachments

NARAYANK991

Excel Ninja
Hi ,

I cannot explain the internals of the INDIRECT function , and the reason why :

=SUM(INDIRECT("Test_Hard"))

works , while :

=SUM(INDIRECT("Test_Offset_Dynamic"))

does not.

Probably this is something that only a Microsoft representative can explain ! As far as I can see , the INDIRECT function will work if the parameter passed to it is a valid address string , period.

Try this :

Define a named range Test_Offset_Dynamic_String as follows :

="$A$2" & ":" & CELL("address",OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-2,0))

You will agree that this is dynamic ; with the existing data , it contains the string $A$2:$A$4 ; add a new value in A5 , and the above range will change to $A$2:$A$5.

Now , use the following formula :

=SUM(INDIRECT(Test_Offset_Dynamic_String))

You will get the value 6.

Narayan
 

SirJB7

Excel Rōnin
Hi, polarisking!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
 

Ceriel

New Member
Hi ,

I cannot explain the internals of the INDIRECT function , and the reason why :

=SUM(INDIRECT("Test_Hard"))

works , while :

=SUM(INDIRECT("Test_Offset_Dynamic"))

does not.

Probably this is something that only a Microsoft representative can explain ! As far as I can see , the INDIRECT function will work if the parameter passed to it is a valid address string , period.

Try this :

Define a named range Test_Offset_Dynamic_String as follows :

="$A$2" & ":" & CELL("address",OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A:$A)-2,0))

You will agree that this is dynamic ; with the existing data , it contains the string $A$2:$A$4 ; add a new value in A5 , and the above range will change to $A$2:$A$5.

Now , use the following formula :

=SUM(INDIRECT(Test_Offset_Dynamic_String))

You will get the value 6.

Narayan
Can I implement this in an depended drop down data validation?
And If so how do i do this?
 

NARAYANK991

Excel Ninja
Hi ,

If you can upload your sample file , and explain your requirements , it will be easier to suggest what can be done and how it can be done.

Narayan
 

Ceriel

New Member
This is a sample file I made that shows how far I am now.

The defined names: "initiating" and "preparation" are fixed names the rest are dynamic.
 

Attachments

Top