Return row position of first blank cell in range?

[Ed from AZ]

In XL2007, is there a worksheet function that will return the row
position of the first blank (no formula or data at all) cell in a
named range? I've tried LOOKUP and MATCH with no success.

Ed

 

[T. Valko]

Is the range a single column? Do you want the relative row number (relative
to the range) or the absolute row number (actual row number)?

 

[Ed from AZ]

Is the range a single column?
Yes.

Do you want the relative row number (relative
to the range) or the absolute row number (actual row number)?

Absolute.


Ed

 

[T. Valko]

Try this array formula** :

=INDEX(ROW(rng),MATCH(TRUE,ISBLANK(rng),0))

Where rng is the named range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP

Is the range a single column? Yes.

Do you want the relative row number (relative
to the range) or the absolute row number (actual row number)?

Absolute.


Ed

 

[Ed from AZ]

Absolutely fantastic, Biff!! I had also looked at INDEX and ISBLANK,
but had no idea how to put this all together to get what I needed.
Thank you!!!!

Ed

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Absolutely fantastic, Biff!! I had also looked at INDEX and ISBLANK,
but had no idea how to put this all together to get what I needed.
Thank you!!!!

Ed

 

[Andre]

That was very helpfull, but I would like a formula to return the adres of the
cell, which I can use as part of a range for a sum.

The best thing I can come up with is as follows:

=SUM(AE9:"AE"&INDEX(ROW(B9:B115);MATCH(TRUE;ISBLANK(B9:B115);0))

The endresult I want to achieve and the situation I am dealing with is as
follows:

Row no. 9 untill row "X" contain hours planned in. If a user adds an
employee to the planning, it means the rows containing hours planned in
stretch untill "X+1". Below the last employee planned in, there is an empty
row. Below that emply row another order is described with employees planned
in again.

In row 8 I want to have a sum of all the rows below row 8 untill row "X". As
you can see in the formula above, I let excel look for the first empty row it
finds (in column "B"; in which assigned employees are mentioned) and pass
that row number to the range the sum should use.

But this doesn't work.

Any help would be welcome; Andre

 

[Peo Sjoblom]

Try this


=SUM(AE9:INDEX(AE9:AE65536;INDEX(ROW(B9:B115);MATCH(TRUE;ISBLANK(B9:B115);0))))


entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Andre]

Dear Peo,

Thanks for the reply. When trying out your formula, I found it would skip
the first empty row and jump to the next one. Thus adding the hours of two
orders to the sum. This has something to do with the row() function that you
use.

Frustrated by not getting it done, and thinking in circles, I decided to
build the formula from scratch with the MS help file as reference. The end
result that works is the following matrix formula:

=SUM(AE9:INDEX(AE9:AE206;MATCH(TRUE;ISBLANK($B9:$B206);0)))

The thing that was most confusing was that the index() function returns the
CONTENTS of a cell when used on it's own, but it returns the ADRESS of a cell
when used inside another function such as SUM().

Thank you everyones help. Using the MATCH() and ISBLANK() functions in this
way is really a new insight.

Regards,
Andre