Hi. I have been trying to get my head around some formulas and it has been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want the
result to exceed a certain value. For example, I want the sum of cells A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is more than
128, it pops up with an error box asking me to re-evaluate the numbers I have
entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and send
back to me, so I also want to know if it is possible to protect the formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

KenNZ Wrote:
Hi. I have been trying to get my head around some formulas and it has
been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want
the
result to exceed a certain value. For example, I want the sum of cells
A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is
more than
128, it pops up with an error box asking me to re-evaluate the numbers
I have
entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and
send
back to me, so I also want to know if it is possible to protect the
formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

Hi Ken

For the formula try this
=IF(A1+A2+A4+B5+B6128,"ERROR",A1+A2+A4+B5+B6)

To protect the formulas select cell C15 and use Format Cells
Protection Tab and make sure the Locked box is checked, for the cells
where you want data enterred make sure the Locked box is not checked,
then Tools Protection Protect Sheet, with or without a password


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=486078

Hi Paul,

Thanks very much for your reply. The protection advice works great. Not sure
about the formula, though.

Basically, what I want is a pop-up alert to be shown when they try and enter
more numbers into the cells after the limit of 128 (sum) is reached. How do I
do this?

This sheet is for an electrical lighting plan where clients put the number
of circuits in each room, but the circuits cannot exceed 128. Effectively,
once they have reached a total of 128 circuits, they get a pop up message
asking them to re-evaluate their circuits or give us a call.

How do I do this?

"Paul Sheppard" wrote:


KenNZ Wrote:
Hi. I have been trying to get my head around some formulas and it has
been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want
the
result to exceed a certain value. For example, I want the sum of cells
A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is
more than
128, it pops up with an error box asking me to re-evaluate the numbers
I have
entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and
send
back to me, so I also want to know if it is possible to protect the
formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

Hi Ken

For the formula try this
=IF(A1+A2+A4+B5+B6128,"ERROR",A1+A2+A4+B5+B6)

To protect the formulas select cell C15 and use Format Cells
Protection Tab and make sure the Locked box is checked, for the cells
where you want data enterred make sure the Locked box is not checked,
then Tools Protection Protect Sheet, with or without a password


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=486078