Indirect reference to range

  • Thread starter Thread starter Ryan Poth
  • Start date Start date
R

Ryan Poth

I should probably know this, but I don't, and I fear the
answer is "it cannot be done" or "you've got to write your
own UDF", which I'd prefer not to do in this case.

I am trying to return data from range referenced by a name
which is actually a formula. This is best explained using
an example.

I have the following names in my "Data" worksheet:

StartRow=Data!$A$3
NumRows=Data!$A$2
MyRange=OFFSET(StartRow,1,0,NumRows,1)

I need to reference the data indirectly by specifying the
name (there are many such names in the worksheet). So to
reference the 2nd cell in the range, I need to do
something like:

=OFFSET(INDIRECT("MyRange"),1,0,1,1)

This works fine if "MyRange" is a direct reference to a
range (ie MyRange=Data!$A$2:$A$10), but returns #REF for
the dynamic name in this instance.

It appears to me that the problem is that the INDIRECT
function relies on the name being a direct range
reference, and not a formula. But is there another way to
do this?

Thanks in advance for any help you can provide.

Ryan
 
Can't you use

=OFFSET(MyRange,1,0,1,1)

The only reason to use indirect would be if the named dynamic range would be
in a cell
 
Peo,

It needs to be indirectly referenced in my case. Assume
for the sake of this example that a user will enter the
name of the range in a cell and I need to return elements
of the (referenced) range he enters.

Regards, Ryan
 
Ok, I suspected that.. I guess a UDF would be the only way which of course
is a problem
if this is to be used by different users
 
Back
Top