Excel - Array Formula to skip empty cells
arrays, excel, formula, if-statement
Solution
I think the problem is that you have the range as `B4:$B$12`, so because there are no $ signs around `B4` that reference changes as you drag the formula down the column.......but the formula looks a little more complex than you need so I'd rather suggest a better alternative:
Which version of Excel are you using?
In Excel 2007 or later try this version
`=IFERROR(INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>"",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))),"")`
Assumes your first cell is `D4` (change `ROWS(D$4:D4)` part to match your actual start cell) and you are dragging the formula down the column
confirmed with CTRL+SHIFT+ENTER
In earlier versions where `IFERROR` function is not available, you can use this version
`=IF(ROWS(D$4:D4)>COUNTA(B$4:B$12),"",INDEX(B$4:B$12,SMALL(IF(B$4:B$12<>"",ROW(B$4:B$12)-ROW(B$4)+1),ROWS(D$4:D4))))`
Problem
I have an array formula to fill in the cell when it contains text but I want it to ignore the cell if its blank and go to the next one. The issue I am having is that it does not do it in the order i have for example: Range is ``` -A -(BLANK CELL) -(BLANK CELL) -D -E -F -(BLANK CELL) -H -(BLANK CELL) ``` Result from the formula ``` -A -E -F -H ``` It is skipping the cells that contain letters. CAN SOMEONE HELP?? FORMULA: `{=IF(ISERROR(SMALL(IF(B4:$B$12<>"",ROW(B4:$B$12)),ROW(B4:$B$12)-3)),"",INDEX(B4:$B$12,MATCH(SMALL(IF(B4:$B$12<>"",ROW(B4:$B$12)),ROW(B4:$B$12)-3),IF(B4:$B$12<>"",ROW(B4:$B$12)),0)))}`