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

 

[Jonmo1]

The "Ignore blanks" in data validation does not mean it will remove blanks from the list.

It basically means that a Blank will be allowed as a valid entry in the cell.


You have to create another list that does not include the blanks..

 

[rdsmit1]

I cant get rid of the blank cells in between the selections in my sheet though. So is another work around so my drop down list will not contain blanks?

Thanks,

-Ryan

 

[John_Clayborn]

I cant get rid of the blank cells in between the selections in my sheet though. So is another work around so my drop down list will not contain blanks?

Yes, Ryan. As a general rule data validation works best with lists that already have the blanks removed and are sorted alphabetically or numerically. This is especially true if you plan on cross referencing Excel with other database programs or applications. I make it standard practice to resort the list each time I add something.

 

[rdsmit1]

I have fixed set of data though, that I CANNOT remove the blank cells from. Is there a smart way (meaning dynamic and automatic) to resort a column of data removing the blank cells?

 

[John_Clayborn]

I have fixed set of data though, that I CANNOT remove the blank cells from. Is there a smart way (meaning dynamic and automatic) to resort a column of data removing the blank cells?

Depending on the situation another workaround might work....

Go to another column that you aren't using., say Column T for example.
In T1 you would =B1 (or where ever "dog" is) and then in T2 you would write =B5 (or where ever "cat" is), etc. Then, create your data validation list from the other column. That would be a way that you could have the data with the blank in your original data set, but still also have a data validation friendly version also. If the data in in the original data set changes value (but not location) then the list should update automatically. The drawbacks to doing it this way are that if you change your data set later or you have to add more cells to the column and/or your "legacy" data may be overriden. (for example: B1 was "dog" and I used "dog" a bunch of times in a drop down. But now I've changed "dog" to "horse", everything that used to say "dog" wil change to "horse" even if it was supposed to be Dog. But, most people don't do that, they just add a new box underneath that says "horse" and then expand the data validation list to include it.

 

[rdsmit1]

That wont really help because I need this to be automatically done....

 

[John_Clayborn]

Hmmm. I'm not really sure then. Without knowing more of the context of what you're trying to do or why you're unable to remove the blank cells I'm not sure that I have any other ideas. Maybe someone else know how to do what you're asking... Good luck at the same!

 

[Jonmo1]

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

 

[rdsmit1]

I believe I followed your instructions accurately, but I cant seem to get it to work. In the link below I have added my sheet I am working on like you suggested. I could email it to you if it would help also.

C:\Users\rsmith\Documents\EJHTMLe\TempJean.htm

Thanks!

-Ryan