INDIRECT : how do I make it work

[zentex]

Hi,
The following formula gives me the error message #REF!

=INDEX(INDIRECT("R7"),MATCH(V6,D8:D11,1),11)

The problem I believe is in the INDIRECT("R7") as the following formula works

=INDEX(Well_AA_09,MATCH(V6,D8:D11),1),11)

The content of cell R7 is the text Well_AA_09 which is the name of a dynamic range I have created and pasted from within VBA into cell R7.

Any idea how I can do this right? i.e. refer to the range name by referring to a cell which contains the name

This is my first post here.
Thanks in advance.

 

[DonkeyOte]

if R7 contains the reference you don't need to encapsulate in quotations

ie

=INDIRECT(R7)

 

[zentex]

if R7 contains the reference you don't need to encapsulate in quotations

ie

=INDIRECT(R7)

Thanks. I tried it as below but I still got the error message

=INDEX(INDIRECT(R7),MATCH(V6,D8:D11,1),11)

If I just use the formula =INDIRECT(R7) I get the message #REF! but if I use =INDIRECT("R7") I got Well_AA_09 as the result. That's why I used INDIRECT("R7") in my formula.

 

[Richard Schollar]

Hi

INDIRECT and the DNR produces this problem - you'll need to do away with Indirect and code the DNR directly into the formula instead (or use a static named range, or address)

 

[DonkeyOte]

just to check R7 = Well_AA_09 and not "Well_AA_09"

I'm struggling to replicate the error...

Oh sorry -- didn't read the bit about VBA... thanks Richard.

 

[zentex]

Yes, that's correct. That's what I see within the formula bar

 

[Richard Schollar]

It seems you can use a UDF in place of the Indirect function such as:

Function myRng(r As Range) As Range
Set myRng = Range(r.Value)
End Function

Then use in a cell like:

=INDEX(myRng(D1),5,1)

 

[cornflakegirl]

=INDEX(INDIRECT(R7),MATCH(V6,D8:D11,1),11)

I just tested a version of this, and it worked fine.
Have you definitely got 11 columns in your named range?

 

[zentex]

It seems you can use a UDF in place of the Indirect function such as:

Function myRng(r As Range) As Range
Set myRng = Range(r.Value)
End Function

Then use in a cell like:

=INDEX(myRng(D1),5,1)

It works. Thanks!!!!

 

[Richard Schollar]

Hello Emma

What version of xl are you using? Are you using a dynamically named range rather than a static one?