Data Validation List-Doesn't Ignore Blank Cells

[rdsmit1]

I am creating a data validation list, and I see the option to ignore blank cells and I check that box, but then when I create my list box there is still blank selection options that show up. For exampe my data list I select would be:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17 width=64>dog</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=17>cat</TD></TR></TBODY></TABLE>

Then in the drop down there would be 3 blank selections in between dog and cat. Any ideas?

Thanks!

-Ryan

 

[rdsmit1]

Well I got it to work when my data was entered into column format rather than across rows, I think I can make this work so thanks big!!! Is there an easy change to make your method you described above work across rows rather than columns?

-Ryan

 

[Jonmo1]

It may be easier using the VBA solution provided in that link.

copy the code from that link into a new VBA module.

Then Highlight the range you want to put the NEW list
And enter
=NoBlanks(A1:K1)
And press CTRL + SHIFT + ENTER

Make sure there are the same # of cells in the range you select, and the range you refer to.

If your original list is in A1:K1 (that is 11 cells)
the select 11 cells, say from A2:A12 (that is also 11 cells).

Then enter the formula and press CTRL + SHIFT + ENTER

 

[shmolf]

First, remove any spaces between the elements via:

=INDEX($Range:$Source,MATCH(0,COUNTIF($New:List,$Range:$Source),0))
Then CTRL-SHIFT-ENTER
Example:
My source list that contains blanks is A1:A5. So I start my new list that won't contain spaces in column B. In B2, I would type:
=INDEX($A$1:$A$5,MATCH(0,COUNTIF($B$1:$B1,$A$1:$A$5),0))
Then CTRL-SHIFT-ENTER

The reason for starting the list in B2, is that the formula compares the list above the selected cell with the source list. And the first output is commonly "0". Copy the formula down to lower cells.

----------

Now lets say that the second list will dynamically change in height, and you don't want blanks in your Data Validation list. Use this formula is the "Source:" field after selecting "List" from the Data Validation window.

I will use the previous example, assuming the second list range is B2:B5, with no initial zeros (if there is an initial zero, then the range is B3:B5).
=OFFSET($B$2:$B$5,0,0,COUNTA(($B$2:$B$5)-COUNTBLANK(($B$2:$B$5),1)

I am creating a data validation list, and I see the option to ignore blank cells and I check that box, but then when I create my list box there is still blank selection options that show up. For exampe my data list I select would be:

[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl69, width: 64, bgcolor: transparent"]dog[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]cat[/TD]
[/TR]
</tbody>[/TABLE]


Then in the drop down there would be 3 blank selections in between dog and cat. Any ideas?

Thanks!

-Ryan

 

[Joebund]

I know this is old, but awesome workaround nonetheless, thanks!!

Unfortunately, this problem is not so easy to resolve as one might think....

You must CREATE a new list based on the original.
The NEW list will not contain blanks.
Then your data validation refers to the NEW list..


Here's a link that shows 1 way to create the new list
Eliminating Blank Cells In A Range

Example, if your original list is in A1:A100
And you want to put the NEW list (without blanks) in B1:B??

Create 2 named ranges (Insert - Name - Define), one for each.
Name A1:A100 BlanksRange
Name B1:B100 NoBlaksRange

IMPORTANT
Both named ranges must be the same size.


Then enter this formula in the first cell of the NoBlanksRange

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

IMPORTANT
This is an array formula that must be confirmed with CTRL + SHIFT + ENTER

Then Fill that formula down as far as required.

Now make your data validition refer to =NoBlanksRange

 

[excelife]

Unfortunately, this problem is not so easy to resolve as one might think....

You must CREATE a new list based on the original.
The NEW list will not contain blanks.
Then your data validation refers to the NEW list..


Here's a link that shows 1 way to create the new list
Eliminating Blank Cells In A Range

You know of any way to do it faster? I have 70 lists consolidated by array formulas. I have a button that I've set up to update all the information when clicked but it takes 3-4 minutes to load every time.

 

[ThatExcelGuyKyle]

May I suggest creating a pivot table of your list (make the pivot select all data to absolute bottom of the list columns if this list will be added to). Then you can filter the blanks out and edit the table to only show the list items (No GT line, etc).
This worked for me so if you have any questions, please let me know.

Best Regards,
Kyle

 

[ThatExcelGuyKyle]

Note: With my method, there will still be blanks on the validation, but they will always be at the bottom and any list values will be at the top so there will be no spaces between your list items.

 

[SevenDP]

2019 now, but found this awesome post, thank you

Unfortunately, this problem is not so easy to resolve as one might think....

You must CREATE a new list based on the original.
The NEW list will not contain blanks.
Then your data validation refers to the NEW list..


Here's a link that shows 1 way to create the new list
http://www.cpearson.com/excel/NoBlanks.aspx

Example, if your original list is in A1:A100
And you want to put the NEW list (without blanks) in B1:B??

Create 2 named ranges (Insert - Name - Define), one for each.
Name A1:A100 BlanksRange
Name B1:B100 NoBlaksRange

IMPORTANT
Both named ranges must be the same size.


Then enter this formula in the first cell of the NoBlanksRange

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

IMPORTANT
This is an array formula that must be confirmed with CTRL + SHIFT + ENTER

Then Fill that formula down as far as required.

Now make your data validition refer to =NoBlanksRange