Formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KenNZ - 17 Nov 2005 22:47 GMT

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

Reply to this Message

Paul Sheppard - 17 Nov 2005 23:03 GMT

KenNZ Wrote:

> Hi. I have been trying to get my head around some formulas and it has
> been
[quoted text clipped - 20 lines]
> Cheers
> Ken

Hi Ken

For the formula try this
=IF(A1+A2+A4+B5+B6>128,"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

Signature

Paul Sheppard

Reply to this Message

KenNZ - 17 Nov 2005 23:27 GMT

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?

> KenNZ Wrote:
> > Hi. I have been trying to get my head around some formulas and it has
[quoted text clipped - 31 lines]
> where you want data enterred make sure the Locked box is not checked,
> then Tools > Protection > Protect Sheet, with or without a password

Reply to this Message