# Return next non-Blank Cell in a Column ?

G

#### 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

#### 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

G

#### Guest

Amazing, just amazing !
Thank you
--
jake

T. Valko said:
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

#### T. Valko

You're welcome. Thanks for the feedback!

Biff

Jakobshavn Isbrae said:
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