I have blocks of data in a sheet, every time there is an entry in column A a
new range begins and (in terms of rows) goes as far as until there is another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

Luis,

If the values are contiguous, then the simplest to use and understand would
be

=MATCH(A9,$A$41:$A$10000,0)+COUNTIF($A$40:$A$10000 ,A9)+39

If they are not contiguous, then array enter (enter using Ctrl-Shift-Enter)

=MAX(($A$40:$A$10000=A9)*ROW($A$40:$A$10000))

HTH,
Bernie
MS Excel MVP


"LuisE" wrote in message
...
I have blocks of data in a sheet, every time there is an entry in column A
a
new range begins and (in terms of rows) goes as far as until there is
another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need
to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

And I should have noted that you might want to change the +40 and the +39 in
your formula and in my first formula to

ROW($A$41)-1 or ROW($A$41)-2

to make the formulas properly respond to row insertions or deletions in the
range A1:A40.

Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Luis,

If the values are contiguous, then the simplest to use and understand
would be

=MATCH(A9,$A$41:$A$10000,0)+COUNTIF($A$40:$A$10000 ,A9)+39

If they are not contiguous, then array enter (enter using
Ctrl-Shift-Enter)

=MAX(($A$40:$A$10000=A9)*ROW($A$40:$A$10000))

HTH,
Bernie
MS Excel MVP


"LuisE" wrote in message
...
I have blocks of data in a sheet, every time there is an entry in column A
a
new range begins and (in terms of rows) goes as far as until there is
another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need
to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

Thanks Bernie

"Bernie Deitrick" wrote:

And I should have noted that you might want to change the +40 and the +39 in
your formula and in my first formula to

ROW($A$41)-1 or ROW($A$41)-2

to make the formulas properly respond to row insertions or deletions in the
range A1:A40.

Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Luis,

If the values are contiguous, then the simplest to use and understand
would be

=MATCH(A9,$A$41:$A$10000,0)+COUNTIF($A$40:$A$10000 ,A9)+39

If they are not contiguous, then array enter (enter using
Ctrl-Shift-Enter)

=MAX(($A$40:$A$10000=A9)*ROW($A$40:$A$10000))

HTH,
Bernie
MS Excel MVP


"LuisE" wrote in message
...
I have blocks of data in a sheet, every time there is an entry in column A
a
new range begins and (in terms of rows) goes as far as until there is
another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need
to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

Bernie,

What if I need to find the corresponding last cell in the next Column (B).
in other words, the last non empty cell in B begining from the last non empty
row in A

Thanks in advance

"LuisE" wrote:

I have blocks of data in a sheet, every time there is an entry in column A a
new range begins and (in terms of rows) goes as far as until there is another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

Luis,

You need to better describe your worksheet's structure, but a solution is
possible...

HTH,
Bernie

"LuisE" wrote in message
...
Bernie,

What if I need to find the corresponding last cell in the next Column (B).
in other words, the last non empty cell in B begining from the last non
empty
row in A

Thanks in advance

"LuisE" wrote:

I have blocks of data in a sheet, every time there is an entry in column
A a
new range begins and (in terms of rows) goes as far as until there is
another
entry in Column A.

All the entries in Column A are in A9 with "validation data list" i need
to
retireve the last row of the range of the selected entry using built in
formulas.

I have this to get the first row and it works
=MATCH( A9,$A$41:$A$10000,0)+40

Need the one to find the last row of that range which is delimited by the
next entry in ColA
Thanks in advance

Bernie,
Thanks again for your help and patience.

Let's say that te following ranges hold my data

"A50" "B50:F60"
"A63" "B63:F80"
"A100" "B100:F135"

In "A1" I have a validation list with the contents of ColA (it works just
fine)
In "A2" I have a formula that returns the row number (in"A50:A100") of the
selected value in A1. It works just fine.

In "A3" I need a formula to return the last row in ColF associated to the
selectd value in A1. ie:

if A1 show the value of A50 then A2=50 A3=60
if A1 show the value of A63 then A2=63 A3=80
if A1 show the value of A100 then A2=100 A3=135

Luis,

In A2, array enter (enter using Ctrl-Shift-Enter) the formula

=MIN(IF(OFFSET($F$1,MAX((A3:A10000=A1)*ROW(A3:A100 00))-1,0,10000,1)="",
ROW(OFFSET($F$1,MAX((A3:A10000=A1)*ROW(A3:A10000))-1,0,10000,1)),10001))-1

HTH,
Bernie
MS Excel MVP


"LuisE" wrote in message
...
Bernie,
Thanks again for your help and patience.

Let's say that te following ranges hold my data

"A50" "B50:F60"
"A63" "B63:F80"
"A100" "B100:F135"

In "A1" I have a validation list with the contents of ColA (it works just
fine)
In "A2" I have a formula that returns the row number (in"A50:A100") of
the
selected value in A1. It works just fine.

In "A3" I need a formula to return the last row in ColF associated to the
selectd value in A1. ie:

if A1 show the value of A50 then A2=50 A3=60
if A1 show the value of A63 then A2=63 A3=80
if A1 show the value of A100 then A2=100 A3=135