Help on formula

[Yvonne]

Hi,

I need some assistance in a order form which i am currently doing up. There
is a section on the order where you can enter the quantity and the quantity
must be in lots of twos. I was just wondering if there is any way, when you
enter a quantity and if the quantity is not in lots of two a message can pop
up saying "Please enter in multiples of 2 units" or if the value they enter
in is not numerical a message can pop up saying something like "Please enter
a numerical value". If someone can point me into the right direction it would
be much appreciated. Looking forward to hear from you soon!

Kind Regards
Yvonne

 

[T. Valko]

You can do this using data validation.

Select the cell in question. Assume this is for cell A1.
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(A1>1,MOD(A1,2)=0)

Select the Error Alert tab. There you can enter a message that pops up if a
user enters an invalid entry.

The above formula will only allow entries that are numbers and multiples of
2.

 

[MartinW]

Hi Yvonne,

Data Validation will do that.

For the first one select your cell or (range of cells) and go to
Data>Validation
On the Settings Tab select Custom from the Allow dropdown
Then put this formula in the Formula box =MOD(A1,2)=0
Then click on the Error Alert Tab
Place your message in the Error message Box and OK out.

For the second one do the same process but instead of
selecting Custom, select Whole Number from the dropdown
and set a minimum and maximum value.

Unfortunately you can't have more than 1 Validation per cell.

Also that process is to put it in A1, if you were to select A1:A10
before starting then the formula will update automatically.

Similarly if you want to put it in C10,C12,C14,C16,C18 + C20
then selecct C12 first folllowed C14 C16 etc. and make C10
the last cell you select. Then your formula will be =MOD(C10,2)=0
Excel will automatically adjust the formulae in the other cells.

HTH
Martin