Hi,
You seemed to have found the answer but here is my version..

If your data is in A1 to A100 then you enter this formula in
B1 (Control-Shift-Enter) and copy it down till B100
=OFFSET($A$1,SMALL(--($A$1:$A$100<"")*ROW($A$1:$A$100),COUNTIF($A$1:$A $100,"")+ROW())-1,0)

Alok

"ExcelMonkey" wrote:

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