Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / November 2006

Tip: Looking for answers? Try searching our database.

"Ignore Blank" ignored while validating

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
AA Arens - 19 Nov 2006 11:18 GMT
I have defind a list via Insert > Name. If I validate a cell via Data >
Validation and choose list, list name, I still have emty value in the
combo box while the toggle "Ignore Empty" was active.

Bart
Excel 2003
Bob Phillips - 19 Nov 2006 12:16 GMT
Uncheck it, then blanks are not allowed to be input.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I have defind a list via Insert > Name. If I validate a cell via Data >
> Validation and choose list, list name, I still have emty value in the
> combo box while the toggle "Ignore Empty" was active.
>
> Bart
> Excel 2003
damorrison - 19 Nov 2006 12:19 GMT
you could run a little macro like this to sort your range

Sub Macro1()
   Range("list2").Sort Key1:=Range("H1"), Order1:=xlAscending,
Header:=xlGuess, _
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

dropdown menu works best with a sorted list
Dave Peterson - 19 Nov 2006 12:23 GMT
You can always use edit|clearcontents to empty a cell with data|validation.

This describes what that option is used for--it's taken from VBA's help (not
excel's):

If the IgnoreBlank property is True, cell data is considered valid if the cell
is blank, or if a cell referenced by either the MinVal or MaxVal property is
blank.

===
So if you turn on the formula auditing toolbar and click on the "circle invalid
data" icon, you'll see a circle based on that setting.

And this is from excel's help:

Note  If your allowed values are based on a cell range with a defined name, and
there is a blank cell anywhere in the range, setting the Ignore blank check box
allows any values to be entered in the validated cell.

This is also true for any cells referenced by validation formulas: if any
referenced cell is blank, setting the Ignore blank check box allows any values
to be entered in the validated cell.

> I have defind a list via Insert > Name. If I validate a cell via Data >
> Validation and choose list, list name, I still have emty value in the
> combo box while the toggle "Ignore Empty" was active.
>
> Bart
> Excel 2003

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.