Loading Column Data with blank Rows into Data Validation Box

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$100,SMALL(IF($B$2:$B$100<>0,ROW($B$2:$B$100),""),ROW())),"")

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

So I came across Chip Pearsons answer to this:

http://www.cpearson.com/excel/noblanks.htm

But I cannot get it to work. I have named two ranges:
BlanksRange (B2:B100)
NoBlanksRange (A2:A100)

I then entered this in A2 and pressed CNTRL SHIFT ENTER. A2 is correct but
the rest of the rows have #N/A errors

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(NoBlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))


"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$100,SMALL(IF($B$2:$B$100<>0,ROW($B$2:$B$100),""),ROW())),"")
>
> 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

Got it to work. Not sure how though. I entered into A2 and then auto filled
down.

Thanks

"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$100,SMALL(IF($B$2:$B$100<>0,ROW($B$2:$B$100),""),ROW())),"")
>
> 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

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$100,SMALL(IF($B$2:$B$100<>0,ROW($B$2:$B$100),""),ROW())),"")
>
> 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