Named Ranges - finding the first row

  • Thread starter Thread starter dht
  • Start date Start date
D

dht

I'm creating a named range that automatically detects the first and last row
of a named range depending on the contents of one column. This wouild relate
to say cells B7:B28 have 1 in them and then B29:B47 say 2. The problem is
that the cells with 1 or 2 or whatever in it changes. How can I write a name
range that tracks both the first line and the last line of a particular
number in column B

Thanks
Dave
 
I'm creating a named range that automatically detects the first and last row
of a named range depending on the contents of one column. This wouild relate
to say cells B7:B28 have 1 in them and then B29:B47 say 2. The problem is
that the cells with 1 or 2 or whatever in it changes. How can I write a name
range that tracks both the first line and the last line of a particular
number in column B

Thanks
Dave
Click to expand...

This might work:

Rng1:


=OFFSET(Sheet1!$B$1,MATCH(1,Sheet1!$B:$B,0)-1,1,COUNTIF(Sheet1!$B:$B,1))

explained:

=OFFSET(Sheet1!$B$1,MATCH(number_in_colB,Sheet1!$B:$B,0)-1,
ColumnOffsetFromColumnB,COUNTIF(Sheet1!$B:$B,number_in_colB))

The Match function (-1) computes the offset from Row 1 of the first occurrence
of number_in_ColB. Then the CountIF function counts the number of occurrences,
which will give the height (or number of rows) of the array.

The column offset is used to determine the range that you are interested in --
in the example, it would be Column C.

So if there were 1's in B7:b12, a formula of the type =INDEX(rng1,2) should
return the contents of C8.

HTH,



--ron
 
Back
Top