How to define a dynamic range?

[hrithik]

I have a fixed range "Min" defined from $B$19 to $B26.

I want to make this range dynamic i.e starting at $B$19 but ends only on the last data cell in col B

I tried to use OFFSET(Data!$B$19,0,0,COUNTA(Data!$B:$B),1) and it does not work.

any suggestions?

Also, I need a formula for referencing the last cell in the range (for eg: $B$26 if the range is like in line1 of this post)​

 

[sulakvea]

hi, do u have gaps (blanks) in the range? take a look at this, #3

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Sahak]

Hi,

Try this:

=OFFSET(Data!$B$19,0,0,COUNTA(OFFSET(Data!$B$19,0,0):OFFSET(Data!$B$19,65000,0)),1)