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.

Static Named Cell	Dynamic Named Range 1 - Fruit	Dynamic Named Range 2 - Veg	Formula	Result
Fruit	Apples	Lettuce	=index(Static_Named_Cell,1)	Apples
	Oranges	Cucumber
	Pears	Potatoes

<tbody>
</tbody>

 

[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,"<>")).