Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Validation (2003 sp2)

    If I am in a cell and I enter data, then use the mouse to click on another cell, the data entry is accepted.

    I am validating a cell using a formula. When the users enter data into that cell, and then click on another cell, the validation error triggers the alert box. If the user hit the <ENTER> key, or <TAB> key, all works well. For me, this is a non issue, but I had a user call today to tell me my spreadsheet was broken. I assume I am stuck with this behavior, but thought I would ask about it anyway. Fixing spreadsheets is easier than teaching a user how to enter data!

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

    Re: Using Validation (2003 sp2)

    Sorry, I don't understand.
    If the user enters a value that passes the validation criteria, it should be accepted, whether the user clicks on another cell or presses Tab or Enter.
    If the user enters a value that violates the criteria, an error message should appear, whether the user clicks on another cell or presses Tab or Enter.
    What is the problem exactly?

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

    Re: Using Validation (2003 sp2)

    Could you attach a scaled down workbook that demonstrates the problem, along with a description of exactly what needs to be entered into what cell and when to click on another cell to trigger the problem?
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Validation (2003 sp2)

    Here is the sample.

    I find that if I change an existing value, I can click elsewhere with the mouse. If the cell is initially blank, I cannot click with the mouse.

    I put the wrong instructions in the workbook. You can enter a number between 1 and 93. I believe you can also enter a value begining with an "R".

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

    Re: Using Validation (2003 sp2)

    There are three or four layers of indirectness in the validation, causing delays in the calculation. If you set the validation formula for cell B2 to

    =OR(AND(B2>=1,B2<=93),AND(B2>="R1",B2<="R93"))

    and similar for D2, the problem shouldn't occur.

    (In your version, the validation formula for B2 is =Zone_Start_Validation.
    The defined name Zone_Start_Validation refers to =Lookup!$B$2.
    Lookup!B2 contains the formula =OR(AND(custZoneStart>=1,custZoneStart<=93),AND(cu stZoneStart>="R1", custZoneStart<="R93")).
    The defined name custZoneStart refers to =Data_Entry!$B$2.
    Excel has to evaluate all these defined names before validating)

  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Validation (2003 sp2)

    I am confused why it would evalute fine when <enter> or <tab> is selected but not fine when using the mouse to navigate the speadsheet.

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

    Re: Using Validation (2003 sp2)

    I'm not entirely sure either, but I think the crucial point is that the defined name custZoneStart contains the current value of cell B2, and it hasn't been updated yet with the new value when the user clicks in another cell. By referring to B2 itself, validation does look at the new value.
    Apparently the order of recalculating depends on how you navigate.

Posting Permissions

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