Results 1 to 3 of 3
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Data Validation Percent Weirdness

    I haven't used Data Validation much, and I'm running into strange behavior with percentage inputs. DV Settings on these cells is set as Decimal between zero and 1. The input cells subject to DV are formatted as percentage, so when a user enters "2.1", it is formatted as 2.1% (decimal 0.021).

    These cells take the desired input as any number between zero and 100 until an error is made, and then seem to change format somehow. For instance, in these cells DV accepts user entry of 20 and Excel sets it as 20.0%. But if the user enters a number >100, it errors out, and then the DV Retry will not permit an entry of any whole number; it will now permit only entries of 0 through 1. But then if the user escapes out of the Retry, or enters an acceptable number between zero and 1, the cells can again be entered as a whole number between 0 and 100.

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>(Confused? Me too.)

    Can someone reproduce this? Should I be setting something different? I have a strong preference to retain the percentage formatting for these input cells.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation Percent Weirdness

    JohnBF,

    I reproduced it, as follows:
    1) Format cell as percent, 1 decimal place
    2) Apply validation rule, decimal, between 0 and 1
    3) Type a number between 0 and 100 (result: OK)
    4) Type a number >100 (result: not valid)
    5) Type a value between 1 and 100 (result: not valid
    6) Type a value between 0 and 1 (result: it is accepted, i.e. typing 1 shows "100.0%" and doesn't trigger the validation rule)
    7) Type a value between 0 and 100 (result: OK, back to normal).

    Or, alternate from step 5:
    5) When the validation error message appears, click Cancel (result: you can now type a value between 0 and 100 without triggering the validation error again).

    Apparently, the format is "forgotten" temproarily by the validation loop if it is tripped once, but is "restored" if you cancel. Notice that, if you choose Retry, the cell is in edit mode (like after step 4 above), it is not "starting from scratch" unless you cancel.

    Anyway, I don't know how to really solve this, and I could see how it would be very annoying.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Data Validation Percent Weirdness

    Thanks, Jim. When I'm going nuts, it's nice to have company on the journey.
    <img src=/S/threadhead.gif border=0 alt=threadhead width=28 height=31>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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