Displaying an error message if entries exceed 100%

Forgot to mention that using an "Information" type of error alert is probably
the best because it only has "OK" and "Cancel" options. The "retry" option
which you found to be behaving strangely is only provided in the "Stop" type
of error warning.

"John James" wrote:

>
> The formula needs to work for other rows therefore needs to be:
> =$B10+$C10<=1
> + uncheck the ignore blank box
>
> A curious behaviour:
> Try:
> 1. Format cells as percentage & apply data validation
> 2. Enter 45 in B10 and 55 in C10. These values are correctly stored
> as .45 and .55 due to the formatting
> 3. Enter 56 in C10 to invoke error message - The message correctly
> appears
> 4. Select retry and enter 55 back into c10 and ... the error message
> reappears!!!
> 5. It seems when retry is selected, the percentage format is ignored
> for the revised entries and the 55 is (incorrectly) entered by Excel
> instead of .55. You can see this happening if you enter totals in
> column D
>
> As a workaround, you probably need to add sufficient instructions in
> the error message that appears so that the user is not confused.
>
> Peo Sjoblom Wrote:
> >
> > ...
> > so it should be
> >
> > =$B$10+$C$10<=1
> >
> > you might also want to uncheck ignore blank or else you could put
> > anything
> > in one cell as long as the other is blank
> >
>
>
> --
> John James
> ------------------------------------------------------------------------
> John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
> View this thread: http://www.excelforum.com/showthread...hreadid=528908
>
>

Glad it's working for you.

P.S. Another possible way to avoid the curious behaviour is to avoid the percentage formatting altogether.