Return next non-Blank Cell in a Column ?

[Jakobshavn Isbrae]

Is there a way to link to non-blank cells? For example, if A1 through A10
contained:
a1: John
a2:
a3: Mary
a4: Jane
a5:
a6:
a7: Alice
a8:
a9: Martha
a10: George
a1: John
a2:
a3: Mary
a4: Jane
a5:
a6:
a7: Alice
a8:
a9: Martha
a10: George

then the formulas in B1 through B6 would give:
John
Mary
Jane
Alice
Martha
George

 

[T. Valko]

Try this:

rng = named range = A1:A10

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

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

Biff

 

[Jakobshavn Isbrae]

Amazing, just amazing !
Thank you
--
jake

Try this:

rng = named range = A1:A10

Enter this array formula** in B1 and copy down until you get blanks:

=IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS($1:1))),"")

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

Biff

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Amazing, just amazing !
Thank you