Data validation and percentage format

  • Thread starter Thread starter Billf
  • Start date Start date
B

Billf

I seem to be having some problems with data validation when combined with the
percentage format. Using Excel 2007.

1) Format a cell as a percentage.
2) Apply data validation > decimal > between 0 and 1.
3) If I type 15 it is accepted as valid and 15% appears correctly.
4) If, instead, I simulate an error by typing 155 then validation kicks in
and tells me its outside the range. If I choose Retry then typing 15 now
appears as an error as it is apparently no longer formatted as a percent.
Typing 0.15 at this point displays as 15%.

Any suggestions as to how to avoid this behaviour? Seems to me the
formatting should persist during a retry?

Cheers

Billf
 
Seems to me the formatting should persist during a retry?

Yeah, you would think that. This happens in Excel 2002 as well.

If you click Cancel instead of Retry then it works as it should. The
erroneous entry is cleared and you just re-enter the new value.
 
As a work-around that would be fine, but one of the reasons I want the
validation is to cater for less experienced Excel users. Best not to have a
button called retry and then tell them to ignore it. I'm guessing that there
is no way to suppress the retry button?

For the record it also happens in Office 2003 for Mac (I don't have access
to 2008 for Mac but I'd be surprised if it didn't happen there).

I wonder if anyone has told MS?
 
Back
Top