Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
If cell blank return a blank Angela1979 Excel Worksheet Functions 8 March 7th 07 01:18 PM
Return a value to a cell that is blank hilltop55 Excel Discussion (Misc queries) 2 February 1st 07 08:58 PM
return a blank cell jpotts8117 Excel Worksheet Functions 5 September 23rd 05 08:33 PM
use IF to return a truly blank cell cwinters Excel Discussion (Misc queries) 5 August 6th 05 12:09 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017