Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inconsistant Validation (XL97)

    I am using data validation to allow only one answer per question in a range of five cells per row. (See attachment)
    The Problem:
    If you type a "X" in one of the cells and use the arrow or enter key, the validation works correctly, but if you type a "X" and use the mouse to click on the next row the validation allows more than one answer.
    In other words the validation works different when using keyboard or mouse to accept input.
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Just upon cursory glance, I'd question your strategy. Try this instead, I think it's a lot easier.

    Using cells M10:Q10 as an example, validate each cell in the group with =COUNTA($M$10:$Q$10)<2. You can do them all at once, of course. If the person adds an extra X using the mouse or the keyboard it stops them.

    See your spreadsheet I'm sending back and look at those cells I reference above. I validated them for you (minus the alert text). It should make sense.

    -Lenny
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Select M7:M12.
    Activate Data|Validation.
    Choose Custom for Allow.
    In the formula box enter:

    =COUNTIF($M$7:$M$12,"X")<2

    Click OK.

    Apply the above to other columns.

    The above makes AF7:AH12 superfluous.

    Note that data validation can be rendered ineffective by copying & pasting.

    Aladin
    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Thank You, the way you have done it works fine, but I would still like to know why the validation works inconsistantly the way I have done it in the first place. If I have done something wrong I would like to know what it is.

    Frits

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

    Re: Inconsistant Validation (XL97)

    It doesn't work because the recalculation on the cells your validation is dependent on doesn't take place until after the validation is done.
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Legare
    I don't agree 100% with you. The validation works if I use the enter key after typing in one of the cells. It does not work correctly if I use the mouse to click somewhere else on the sheet after typing in a cell.

    The following quote comes out of the Excel Help under the heading "Determine valid entries for a cell by using a formula":
    "Before evaluating the formula, Microsoft Excel recalculates the worksheet with the data entered in the cell."

    This does not correspond with your statement?

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

    Re: Inconsistant Validation (XL97)

    It does the same on my system. However, on my system, I see the border appear around the cell which I click on before I see the cell with the formula recalculate which indicates to me that the selection change and validation (since the selection would not change if validation failed) were done before the recalculate. That is, IMHO, a bug in Excel code.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Apr 2001
    Location
    Johannesburg
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Thank You for the response.

    Does anyone know if this is a recorded bug and is it also a problem in Office XP?

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inconsistant Validation (XL97)

    Just to confuse things a little further, in your example, I can click on cell n7, press enter to drop to cell n8, press enter again and not get an error message. Also if I click on cell n8 and add an additional "x" so it reads "xx" and press enter it will give the error message, then if you click cancel then enter you can drop to cell n9 even though it is still an error.
    These little boxes we play with every day are just filled with magic, aren't they!!
    Stats

Posting Permissions

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