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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
if R7 contains the reference you don't need to encapsulate in quotations

ie

=INDIRECT(R7)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top