Using Indirect function to return a named range.

**adrianodl** · 08-21-2012, 03:36 AM

Hi there,

I'm trying to create an index function to return values from Tab1, based on a drop down list on Tab2. The result should be returned to cell d6 on Tab3 but I'm not able to build the indirect function in a workable manner.

Tab3D6 is basically reading information on Tab2A1, and building a query to search the named range (Tab1Table1) on Tab1.

Not sure if you guys get it, but once you look at Tab3D6 you'll understand what I'm trying to do.

Any help appreciated.

indirect function.xlsx

**dilipandey** · 08-21-2012, 03:53 AM

Hi adrianodl,

you can use the below formula to correct the reference in Indirect function :-

Formula:

Please Login or Register  to view this content.

I am not sure how and from where you wish to provid erow and column indexes.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**adrianodl** · 08-21-2012, 07:22 AM

Hi Dili,
Unfortunately this solution didn't work. It's not pointing to my named range.

What I'm having issues with is that the following formula:

Please Login or Register  to view this content.

is resolving the indirect as Tab2!A1 but not bringing the actual content of cell Tab2!A1 (Tab1).
The working formula should resolve to something like:

Please Login or Register  to view this content.

Does it make sense?

Cheers!
ADri

**dilipandey** · 08-21-2012, 09:30 AM

Hi ADri..

In my earlier reply, I igonred the defnied names...
Ok.. so in your formula syntax " Tab1!Tab1Table1" which one is the defined name.. I believe in Index you should provide first the Array / reference ... suggest you to do it first without defined names and check if that works and then convert that worked reference in the defined name and use with Index function. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>