Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    data validation not ignoring blanks

    I create a name list, group1, using a formula. The values in the list are names or "".
    Then, the name list is used in a data validation.

    The problem is that the cells with a "" in them show up as blanks in the drop-down.

    So, I might have:

    AAA
    BBB




    CCC

    DDD

    Is there a way to remove those blanks from the drop-down? I have the box "ignore blank cells" checked, but these aren't really blank if they have a "" in them. Hmmm. Does Excel have a REAL blank that you can put in a cell? Something that's really nothing?

    Thanks in advance.

  2. #2
    New Lounger
    Join Date
    Feb 2010
    Location
    East Brunswick, NJ
    Posts
    15
    Thanks
    1
    Thanked 3 Times in 2 Posts
    The ignore blank cells refers to what your user types, not to the data in the dropdown list. Lets the user hit "enter" without generating an annoying error message.
    Norm

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Dear Kweaver:

    Excel's Dialog Box for Data Validation is very poorly written. the "ignore blank cells" should actually read "Allow Users to enter drop down items or alternatively leave the cell blank."
    When the box is checked the user is allowed to skip a data entry cell.

    As to your list, Excel in its documentaiton of Data Validation does say if you want the valuation drop down to be in alpha order do a sort.

    If a sort of the Live Data causes too many issues, then copy data and "Paste Special - Values" in an area and sort this list for your validation. If you have multipe items same idea but use advanced filter, "Unique Items" and copy to a new location, lastly sort that data and use as Validaiton.

    Good Luck.

    TD

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You need to create a contiguous range of the values you want, no blanks cells at all, and assign that to the data validation. You can keep the current list as an intermediate and create a new list with a named range (I used "DataList") that only selects the extracted values.
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    How unfortunate (for me). I have about 50 named ranges that are created this way and will have to create yet another set of ranges that are sorted versions of those columns. YUCK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •