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$65535>0),Sheet1!$A$1:$A$65535),Sheet1!$A$1:$A$65535,0),1)
--
tj
Bookmarks