Displaying an error message if entries exceed 100%

[KG]

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?

[JudithJubilee]

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?

[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




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

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

--
Peo


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

[John James]

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.

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

[KG]

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

[KG]

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

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

[KG]

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?

"JudithJubilee" wrote:

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

[KG]

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

"KG" wrote:

> 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?
>
> "JudithJubilee" wrote:
>
> > 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?

[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




"KG" <KG@discussions.microsoft.com> wrote in message
news:E4516C85-0FAB-4704-800B-09192F37DB4A@microsoft.com...
> 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...
>
> "KG" wrote:
>
>> 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?
>>
>> "JudithJubilee" wrote:
>>
>> > 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?