Displaying an error message if entries exceed 100%

[Guest]

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?

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Pete_UK]

"calendarization" ?

Is this really a word?

Pete

 

[Peo Sjoblom]

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>

 

[John James]

The formula needs to work for other rows therefore needs to be:
=$B10+$C10<=
+ uncheck the ignore blank bo

A curious behaviour:
Try:
1. Format cells as percentage & apply data validatio
2. Enter 45 in B10 and 55 in C10. These values are correctly store
as .45 and .55 due to the formattin
3. Enter 56 in C10 to invoke error message - The message correctl
appear
4. Select retry and enter 55 back into c10 and ... the error messag
reappears!!
5. It seems when retry is selected, the percentage format is ignore
for the revised entries and the 55 is (incorrectly) entered by Exce
instead of .55. You can see this happening if you enter totals i
column

As a workaround, you probably need to add sufficient instructions i
the error message that appears so that the user is not confused

 

[Guest]

I followed your instructions, entering the formula while highlighting B10 +
C10. The error message will appear if the offending data entry is made in
cell B10, however no error message will appear if the offending data entry is
made in cell C10. What am I doing wrong?

 

[Guest]

After implementing the suggestions by Messrs. Sjoblom and James, I was able
to resolve the problem. Thanks for your help!

"Calendarization" was a common accounting term in my previous company. My
spell checker reports an error when I use it but old habits are hard to
break. Sorry to offend...

 

[Peo Sjoblom]

No offense taken <g> Glad you got it working

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Guest]

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.

 

[Guest]

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]

Glad it's working for you.

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