Return next non-Blank Cell in a Column ?  
Author Message
Alcuin





PostPosted: Wed May 23 13:36:34 CDT 2007 Top

worksheet functions >> Return next non-Blank Cell in a Column ?

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


--
jake

Excel264  
 
 
T





PostPosted: Wed May 23 13:36:34 CDT 2007 Top

worksheet functions >> Return next non-Blank Cell in a Column ? 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



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


 
 
JakobshavnIsbrae





PostPosted: Wed May 23 14:09:02 CDT 2007 Top

worksheet functions >> Return next non-Blank Cell in a Column ? 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
>


> > 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
> >
> >
> > --
> > jake
>
>
>
 
 
T





PostPosted: Wed May 23 16:28:29 CDT 2007 Top

worksheet functions >> Return next non-Blank Cell in a Column ? You're welcome. Thanks for the feedback!

Biff



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


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