INDIRECT : how do I make it work

zentex

New Member
Joined
Jul 2, 2008
Messages
20
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
if R7 contains the reference you don't need to encapsulate in quotations

ie

=INDIRECT(R7)
 

zentex

New Member
Joined
Jul 2, 2008
Messages
20
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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.
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Code:
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)
 

zentex

New Member
Joined
Jul 2, 2008
Messages
20
It seems you can use a UDF in place of the Indirect function such as:

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hello Emma

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

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top