# 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.

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.

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

#### 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

• 8.5 KB Views: 62

#### 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 :

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 :

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

• 9.3 KB Views: 19

#### NARAYANK991

##### Excel Ninja
Hi ,

I don't think the DV dropdown can use the dynamic named ranges , though I am not absolutely sure.

Narayan