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.

 

[cornflakegirl]

Yeah. Reading the question properly. Always helpful!
Didn't know about that quirk - thanks

 

[DonkeyOte]

was looking at something similar-ish last night -- would appreciate a better solution (see penultimate post in thread) but couldn't think of it myself... essentially using a variable external range in an index formula without using INDIRECT (nor utilising the indirect.xla for external files)

http://www.mrexcel.com/forum/showthread.php?t=327771