fin last row withtin a range using built functions not VBA

[LuisE]

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 Deitrick]

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

 

[Bernie Deitrick]

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

 

[LuisE]

Thanks Bernie

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

 

[LuisE]

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

 

[Bernie Deitrick]

Luis,

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

HTH,
Bernie

 

[LuisE]

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

 

[Bernie Deitrick]

Luis,

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

=MIN(IF(OFFSET($F$1,MAX((A3:A10000=A1)*ROW(A3:A10000))-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