Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Data Validation not working (2003 SP2)

    Dear Loungers,

    I am doing something simple that I have been doing for years, but it isn't working today!
    I have a sheet with a number of columns all of which have various validation based on lists which are named ranges on another sheet in the same workbook. I am using Data, Validation and specifying the list e.g. "=Release" everything else is left as default. The drop dowm list shows the right values, however I can type any value I like.
    I have done several tests using a completely vanilla XLT and the one my erroneous workbook is based on. All my tests work as I would expect. The only clue I have is a friend who said "oh yes, you can't have the validation list on another sheet" this seems very odd to me as I have always done things with any lookup or reference data on a separate sheet. However, with my erroneous workbook I did move all the lists to the same sheet and it works.

    What is going on?

    liz

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Validation not working (2003 SP2)

    I have not yet tried refering to an external range using range names in the validation rules, so I cannot comment on this, however, have a look at this MS Knowledge Base page...some info may clarify...
    Cannot use external references with data validation in Excel
    Regards,
    Rudi

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (2003 SP2)

    Rudi,

    thank you, I've read this and understand what it is saying. I am doing something similar but it is an awful phaf! I have my reference data sheet with lists, in the actual sheet using them I am adding lines which refer to the lists, these are named ranges and are hidden. This works as I only have on real sheet in this case. But I often have several sheets referring to one reference list so it's not so good then as I will need to have copies of the lists in each sheet.

    What doesn't make sense is that I know I have used this method forever and it's works i.e. validation lists on a single sheet referred to by others. In fact i and other colleagues in the office have all retested this to see if we were all going barking mad. To our relief we aren't. But we are left with the problem of inconsistent behaviour.

    liz

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (2003 SP2)

    List can be on other sheets, but they have to be in a named range. You can not specify a range on another sheet directly in the data validation Source box.

    Your question was not completely clear about what you want to happen when you enter something in the cell. If you want to prevent entering something other than what is in the list, then select the cell and then select Validation from the Data menu. Select the Error Alert tab in the dialog box and put a check mark next to "Show error alert after entering invalid data."
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (2003 SP2)

    Legare,

    I always used named ranges so my Validation Allowpecifies "List" and the Source field refers to e.g. "Release" which is a list on let's say Worksheet2 in a workbook. And it has always worked but as I said today not. And the article Rudi pointed to implys it should not so I guess many of us have been using unsupported and therefore unreliable functionality.

    To clarify, only entries from the list are allowed and as the defaults in the Data, Validation dialogue are set to error in this case - even if you don't enter an error message you will get an error pop-up if you enter something not on the list - I mean normally and not today!

    thank you............ liz

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation not working (2003 SP2)

    In addition to Legare's reply, the Style for the Error Alert must be set to Stop. If it is set to Warning or Info, the user is still allowed to enter values that do not satisfy the validation criteria.

  7. #7
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (2003 SP2)

    Hans,

    Yes I know, it is by default and I did check that I had not already deselcted it inadvertently

    liz

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Validation not working (2003 SP2)

    Does the problem persist if you quit and restart Excel, or restart your PC?

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation not working (2003 SP2)

    That is not how it should work. If you reboot your PC and it still does not work, could you upload a workbook that shows the problem?
    Legare Coleman

Posting Permissions

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