Dynamic Range in Lookup formula not returning expected result

[KirnonBhale]

Hi all,

I hope that this is an easy one for you folks.

I'm trying to return a result with an index formula.

EG. =index(Dynamic_Named_Range,1)

Works fine. However the Dynamic_Named_Range is referenced through a Static_Named_Cell.

I thought a formula like =index(indirect(Static_Named_Cell,1) Where the named cell contains the name of the dynamic range would return an answer but currently I keep getting an error.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Static Named Cell[/TD]
[TD]Dynamic Named Range 1 - Fruit
[/TD]
[TD]Dynamic Named Range 2 - Veg[/TD]
[TD]Formula[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]Fruit
[/TD]
[TD]Apples
[/TD]
[TD]Lettuce
[/TD]
[TD]=index(Static_Named_Cell,1)
[/TD]
[TD]Apples
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oranges
[/TD]
[TD]Cucumber
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pears
[/TD]
[TD]Potatoes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Try

=INDEX(INDIRECT(Static_Named_Cell),1)

 

[KirnonBhale]

Try

=INDEX(INDIRECT(Static_Named_Cell),1)

Thanks for the response. However that works great as long as the Static_Named_Cell reference is to a Static_Named_List. If it references a Dynamic_Named_List it returns an error. So eg. Fruits List uses =OFFSET(Tables!$b$2,0,0,COUNTIF(Tables!$b$2:$b$100,"<>")).