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

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

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

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

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