| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.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 |
|
#2
Posted to microsoft.public.excel.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 "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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
| If cell blank return a blank | Excel Worksheet Functions | |||
| Return a value to a cell that is blank | Excel Discussion (Misc queries) | |||
| return a blank cell | Excel Worksheet Functions | |||
| use IF to return a truly blank cell | Excel Discussion (Misc queries) | |||
Linear Mode
