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$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

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)+1ROWS(NoBlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),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$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

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$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

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