Formulas

[KenNZ]

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

[Paul Sheppard]

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

[KenNZ]

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