I have a Data Validation Box. I need to load column (B2:B100) data into it
from which has rows that have blanks in them. My goal is to somehow redefine
the column data so that the blanks are ignored. Then I wiil use a dynamic
range to pull this new column into the Data Validation Box.

I was trying to do this with:
=IF(COUNTIF($B$2:$B$100,"")=ROW(),INDEX($B$2:$B$1 00,SMALL(IF($B$2:$B$100<0,ROW($B$2:$B$100),""),RO W())),"")

But its not working. I would like the result to look like the illustration
below so that blanks are ignored and no erros are brought into the final
column which feeds the dynamic range.


B C
1 1
3
3 4
4 7
8
7 9

8
9


Thanks