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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 10:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com