How to get a cell to error if the wrong figue is entered

[Eintsein_mc2]

I have a worksheet that is a quote form for quoting on bricks. Bricks
are only avaliable in lots of 66. So if my sales people are giving a
quote say for 3000 bricks this would not be possiable because they only
come in lots of 66, the figue they would have to enter would be 3036. I
was wondering if there is a way to get the cell to show an error if the
figue entered dose not work with multiables of 66. Thanks

 

[Anne Troy]

Why not just have them enter the number of lots instead? Then have your
formula multiply it by 66.
************
Anne Troy
www.OfficeArticles.com

 

[Bryan Hessey]

The easy way would be to use another (adjacent) cell to test the figure
such as in B1 put

=IF(A1-(66*INT(A1/66))<>0,"Qty not divisible by 66 error","")

or

=IF(MOD(A1,66)<>0,"Qty not divisible by 66 error","")


you can set the Font for B1 to be red & bold etc.

 

[Rowan]

You can use data validation.

Select the cell where you want to restrict input, A1 in my example. From
the menus Data>Validation. Select Custom in the Allow dropdown. In the
formula box enter "=MOD(A1,66)=0" (without quotes - change cell
reference to match selected cell).

On the Error Alert tab you can set your own error message eg must be
multiple of 66.

Hope this helps
Rowan

 

[Eintsein_mc2]

Thanks for that Guys, it works perfect, exactly what I needed.