Dynamic Named Range with blank cells
 

I have been using the Dynamic Named Range I've seen on www.contextures.com
and posted here a few times:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

I was looking for an alternative (see below) that would allow for blanks in
the Named Range. I've used a formula I found posted here for locating the
last value in a column to modify the above function. It sems to be working
when I add values into the column and try basic funcitons using values in the
range.
the only restriction I'm sure of is that the start of the range has to be in
Row 1.

Does anyone see a potential pitfall that I'm missing? I don't want to use
this in a live worksheet until I'm certain it's working. Thanks.

=OFFSET(Sheet1!$A$1,0,0,MATCH(LOOKUP(2,1/(Sheet1!$A$1:$A$655350),Sheet1!$A$1:$A$65535),She et1!$A$1:$A$65535,0),1)

--
tj

tj,

It blows up when the last value of the column is not unique: i.e., it is repeated somewhere above in
the column. MATCH only looks for a match, no matter where, and the value that it is looking for is
the last value of the column.

HTH,
Bernie
MS Excel MVP

Does anyone see a potential pitfall that I'm missing? I don't want to use
this in a live worksheet until I'm certain it's working. Thanks.

Thanks, Bernie. I knew some problem must be there somewhere - it looked too
simple.
--
tj


"Bernie Deitrick" wrote:

tj,

It blows up when the last value of the column is not unique: i.e., it is repeated somewhere above in
the column. MATCH only looks for a match, no matter where, and the value that it is looking for is
the last value of the column.

HTH,
Bernie
MS Excel MVP

Does anyone see a potential pitfall that I'm missing? I don't want to use
this in a live worksheet until I'm certain it's working. Thanks.

Try the following...

=OFFSET(Sheet1!$A$2,0,0,MATCH(2,1/(1-ISBLANK(Sheet1!$A$2:$A$65536))))

or

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(2,1/(1-ISBLANK(Sheet1!$A$2:
$A$65536))))

....which eliminates the use of the volatile function OFFSET. Note that
you cannot use whole column references.

Hope this helps!

In article ,
"tjtjjtjt" wrote:

I have been using the Dynamic Named Range I've seen on www.contextures.com
and posted here a few times:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

I was looking for an alternative (see below) that would allow for blanks in
the Named Range. I've used a formula I found posted here for locating the
last value in a column to modify the above function. It sems to be working
when I add values into the column and try basic funcitons using values in the
range.
the only restriction I'm sure of is that the start of the range has to be in
Row 1.

Does anyone see a potential pitfall that I'm missing? I don't want to use
this in a live worksheet until I'm certain it's working. Thanks.

=OFFSET(Sheet1!$A$1,0,0,MATCH(LOOKUP(2,1/(Sheet1!$A$1:$A$655350),Sheet1!$A$1:
$A$65535),Sheet1!$A$1:$A$65535,0),1)