>> Dynamic Range

[Jonathan Parminter]

Hi, I want to use a Dynamic Range Name. From visiting Chip
Pearsons site (excellant site!) I have the formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If the contiguous block of data is A1:A20 then it works
beautifully. However, if the data is not a contiguous
range then it fails.

This function has the range extend from A1 for the number
of non-blank cells. So that if, within the range A1:A20,
there is only 10 cells that are non-blank, then range
referenced is A1:A10. If A20 is the last cell that
contains data, I want the range to reference A1:A20.

I'm having a Pooh bear kind of day and so can't get my
little brain to adjust the offset function to use the last
non-blank cell row instead of range height.

Any ideas or suggestions appreciated

Cheers
Jonathan

 

[Vasant Nanavati]

Perhaps something like:

=OFFSET(Sheet1!$A$1,0,0,MAX((ROW(Sheet1!$A$1:$A$1000)*(Sheet1!$A$1:$A$1000<>
""))),1)

Using $A:$A may not work as this is an array formula.

 

[Jonathan Parminter]

Thanks Vasant, it's excellant

Thanks
Jonathan