Displaying an error message if entries exceed 100%

The problem with that is that you use relative cell references so while it
will work if D10 has for instance
45% then you can't enter 56% in B10 while if the other way around you can
because in C10 the formula is in fact

=C10+D10<=1

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



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"JudithJubilee" <JudithJubilee@discussions.microsoft.com> wrote in message
news:3E093B38-2B95-4EB3-960F-20BCC3084B25@microsoft.com...
> Hello KG,
>
> Highlight B10 and C10. Go to Data + Validation. Choose Custom from the
> drop
> down and type the following in the dialogue box:
>
> =B10+C10<=1
>
> You can add a custom error in the Error Alert tab.
>
> Judith
>
> --
> Hope this helps
>
>
> "KG" wrote:
>
>> My column headings are:
>> A = Description
>> B = 1st Half
>> C = 2nd half
>> D = total year
>>
>> in cells B10 and C10 the user is to enter the "calendarization" %, i.e.,
>> the
>> % of total sales that occur in the first and second half of the year.
>> Because
>> it is redundant, I have NOT aggregated the total in cell D10.
>>
>> I want to display a visual warning if the % entered in B10 and C10 exceed
>> 100%. For now, I have planted an "error" message in the unused cell D10
>> which
>> is remains as long as the total does not exceed 100% but which appears,
>> through Conditional Formatting, in bold red lettering if the total
>> exceeds
>> 100%.
>>
>> Is there any other way to display such an error message, through
>> Validation
>> or VBA?

"calendarization" ?

Is this really a word?

Pete

No it's not but I have stopped correcting spelling/grammar (including
myself) in these newsgroups/forums, that would be a full time position <bg>

--
Peo


"Pete_UK" <pashurst@auditel.net> wrote in message
news:1143937206.584003.244230@e56g2000cwe.googlegroups.com...
> "calendarization" ?
>
> Is this really a word?
>
> Pete
>

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.

Originally Posted by Peo Sjoblom

...
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

I think I overcame the curious behaviour that you described by posting a
warning message that reads"your entries exceed 100%, click cancel to
correct." Directing the user to click "cancel" seems to prevent the strange
response that you reported. This is good enough for me. Thank you for your
help.

"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
>
>

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.