Remember Me?

#1
May 23rd 07, 06:57 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 78
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

#2
May 23rd 07, 07:36 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

"Jakobshavn Isbrae" wrote in
message ...
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

#3
May 23rd 07, 08:09 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 78
Return next non-Blank Cell in a Column ?

Amazing, just amazing !
Thank you
--
jake

"T. Valko" wrote:

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" wrote in
message ...
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

#4
May 23rd 07, 10:28 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
Return next non-Blank Cell in a Column ?

You're welcome. Thanks for the feedback!

Biff

"Jakobshavn Isbrae" wrote in
message ...
Amazing, just amazing !
Thank you
--
jake

"T. Valko" wrote:

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" wrote in
message ...
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM Angela1979 Excel Worksheet Functions 8 March 7th 07 01:18 PM hilltop55 Excel Discussion (Misc queries) 2 February 1st 07 08:58 PM jpotts8117 Excel Worksheet Functions 5 September 23rd 05 08:33 PM cwinters Excel Discussion (Misc queries) 5 August 6th 05 12:09 AM

All times are GMT +1. The time now is 06:53 AM.