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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,156
Messages
5,835,707
Members
430,381
Latest member
tntcute

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