function like INDIRECT to return a range?
 

Hello. I'm using an array formula that will only accept a finite range as a
parameter (e.g. A2:A999). The array formula will not accept a named range or
column reference (e.g. A:A). I'd like to specify the range dynamically
rather than hardcode it, for instance by using MATCH to find the first and
last rows with relevant data.

Something like INDIRECT would be perfect, but INDIRECT can only return a
cell reference, not a range (right?).

Any ideas? Thanks!

function like INDIRECT to return a range?
 

Maybe you can use a range that grows and contracts depending on the entries:
http://contextures.com/xlNames01.html#Dynamic
(from Debra Dalgleish's site)

Wilba wrote:

Hello. I'm using an array formula that will only accept a finite range as a
parameter (e.g. A2:A999). The array formula will not accept a named range or
column reference (e.g. A:A). I'd like to specify the range dynamically
rather than hardcode it, for instance by using MATCH to find the first and
last rows with relevant data.

Something like INDIRECT would be perfect, but INDIRECT can only return a
cell reference, not a range (right?).

Any ideas? Thanks!

--

Dave Peterson

function like INDIRECT to return a range?
 

Hi,

If your data are contiguous in the column(s) referenced in the array
formula, the following method may work.

Supposing your data range is in A2:A999:
In an empty cell (say P2) enter the formula, ="A"&COUNT(A:A)+1 (The '1' at
the end corresponds to one less than the starting row number of your data
range. You have to modify that number if the range starts at some other row).
Now P2 will show the cell reference for the last row (in this example, A999)
In your array formula enter A2:INDIRECT(P2) to mimic the actual range, i.e.,
A2:A999; however, the formula will be dynamic and if more rows are added to
column A, the INDIRECT function will take care of it.

I hope this works!

Best regards,
B. R. Ramachandran

"Wilba" wrote:

Hello. I'm using an array formula that will only accept a finite range as a
parameter (e.g. A2:A999). The array formula will not accept a named range or
column reference (e.g. A:A). I'd like to specify the range dynamically
rather than hardcode it, for instance by using MATCH to find the first and
last rows with relevant data.

Something like INDIRECT would be perfect, but INDIRECT can only return a
cell reference, not a range (right?).

Any ideas? Thanks!

function like INDIRECT to return a range?
 

Dave Peterson wrote:

Maybe you can use a range that grows and contracts depending on the
entries:
http://contextures.com/xlNames01.html#Dynamic
(from Debra Dalgleish's site)

That's cute, I hadn't seen that before.

I got what I want by using MATCH in the OFFSET formula to find the first and
last occurrences of a start date and end date, then I used the dynamic range
in the array formula.

Superb, thanks a bunch!


Wilba wrote:

Hello. I'm using an array formula that will only accept a finite range as
a
parameter (e.g. A2:A999). The array formula will not accept a named range
or
column reference (e.g. A:A). I'd like to specify the range dynamically
rather than hardcode it, for instance by using MATCH to find the first
and
last rows with relevant data.

Something like INDIRECT would be perfect, but INDIRECT can only return a
cell reference, not a range (right?).

Any ideas? Thanks!

function like INDIRECT to return a range?
 

B. R.Ramachandran wrote:

If your data are contiguous in the column(s) referenced in the array
formula, the following method may work.

Supposing your data range is in A2:A999:
In an empty cell (say P2) enter the formula, ="A"&COUNT(A:A)+1 (The '1'
at
the end corresponds to one less than the starting row number of your data
range. You have to modify that number if the range starts at some other
row).
Now P2 will show the cell reference for the last row (in this example,
A999)
In your array formula enter A2:INDIRECT(P2) to mimic the actual range,
i.e.,
A2:A999; however, the formula will be dynamic and if more rows are added
to
column A, the INDIRECT function will take care of it.

I hope this works!

It would work for the way you have interpretted the question, but I didn't
make it clear what I meant - sorry, my fault.

A2:A999 is not the range I want, it's just an example of a the kind of
finite range that the array formula will accept.

The actual range I want is a subset from a column of dates in ascending
order, which includes everything from the first occurence of an arbitrary
start date to the last occurence of an arbitrary end date. Dave Peterson's
solution works brilliantly for what I want.

Thanks very much for taking the time to help me!


"Wilba" wrote:

Hello. I'm using an array formula that will only accept a finite range as
a
parameter (e.g. A2:A999). The array formula will not accept a named range
or
column reference (e.g. A:A). I'd like to specify the range dynamically
rather than hardcode it, for instance by using MATCH to find the first
and
last rows with relevant data.

Something like INDIRECT would be perfect, but INDIRECT can only return a
cell reference, not a range (right?).

Any ideas? Thanks!