My column headings a
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?

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

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" wrote in message
...
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 a
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" wrote in message
oups.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.

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

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

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

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" wrote in message
...
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 a
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?

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.


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