Return next non-Blank Cell in a Column ?

[Guest]

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

 

[Guest]

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

 

[v_night]

Question, In reference to this great answer- let's say you want information in the blank cells row to populate on the next non blank cells row. How would you do this?

For example:

A1 B1
Jon Blue

A2 B2
---- Red

A3 B3
Mary Green

A4 B4
Jane Purple

A5 B5
------ Blue

A6 B6
Alice Red
...and so on...

Formula =


A1 B1
Jon Red

A2 B2
Mary Green

A3 B3
Jane Blue

A4 B4
Alice Red