I have a row that contains about 26 cells and holds percentages. I want an
error message to display when the sum of those 26 cells goes over 100%. I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!

First suggestion.. try by changing =BQ16100 to =BQ161
as %'s total to 1.00.

"JICDB" wrote:

I have a row that contains about 26 cells and holds percentages. I want an
error message to display when the sum of those 26 cells goes over 100%. I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!

Well, it depends on how you want this to work.

Since the cell (presumably) contains a SUM formula you can't use data
validation.

You could use a formula like this:

=IF(SUM(A1:A5)100,"Error: "&SUM(A1:A5)&" exceeds 100",SUM(A1:A5))

Or, you could use a separate cell:

A10 = SUM formula
A11 = =IF(A10100,"Error: sum exceeds 100","")

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
I have a row that contains about 26 cells and holds percentages. I want an
error message to display when the sum of those 26 cells goes over 100%. I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!

I tend to use conditional formatting for this type of thing. You can set
the colour of the cell to red if it is over 1 or green if it is under.
Makes it very easy to see at a glance.
"T. Valko" wrote in message
...
Well, it depends on how you want this to work.

Since the cell (presumably) contains a SUM formula you can't use data
validation.

You could use a formula like this:

=IF(SUM(A1:A5)100,"Error: "&SUM(A1:A5)&" exceeds 100",SUM(A1:A5))

Or, you could use a separate cell:

A10 = SUM formula
A11 = =IF(A10100,"Error: sum exceeds 100","")

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
I have a row that contains about 26 cells and holds percentages. I want
an
error message to display when the sum of those 26 cells goes over 100%.
I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!

I tried Jim's suggestion, and it didn't work. I think because as Valko
stated the validation doesn't work on sums. I then tried both Valko's
validation formula and Steve's conditional method. I couldn't get either to
work at first but then I realized that I was making the formula relative and
not absolute so it kept moving the range. I finally got both to work but I
think I will opt for the error message because it flags the user and not as
subtle as a color change.

Jim, T.Valko, and Steve - thanks for your help.

"JICDB" wrote:

I have a row that contains about 26 cells and holds percentages. I want an
error message to display when the sum of those 26 cells goes over 100%. I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JICDB" wrote in message
...
I tried Jim's suggestion, and it didn't work. I think because as Valko
stated the validation doesn't work on sums. I then tried both Valko's
validation formula and Steve's conditional method. I couldn't get either
to
work at first but then I realized that I was making the formula relative
and
not absolute so it kept moving the range. I finally got both to work but
I
think I will opt for the error message because it flags the user and not
as
subtle as a color change.

Jim, T.Valko, and Steve - thanks for your help.

"JICDB" wrote:

I have a row that contains about 26 cells and holds percentages. I want
an
error message to display when the sum of those 26 cells goes over 100%.
I
didn't know how to do this so based on some suggestions I found in the
archives here I first created a total cell at the end of the row and then
used this formula in the 26 cells of the row =BQ16100.

I feel stupid cause I'm sure this is so easy it will bite me on the nose.
Can someone help me. Thanks!