Limit order quantity to multiple of minimum order quantity

[berniean]

I have a price list with Minimum Order Quantity in one column and a blank cell in the next column for the customer to fill in. Sales has requested that I restrict the data entry by the customer to a multiple of the MOQ, with an appropriate error message for mistakes. I know how to do certain types of Data Validation, but not this. Is there any way to accomplish this without using VBA? I have very limited VBA knowledge.

Any help is greatly appreciated.

 

[S.H.A.D.O.]

Hi berniean and welcome to the Forum.

Have you tried putting limits via validation?
If you press Alt + D + L it will bring up the Data Validation box where you can enter the criteria and appropriate error message.

I hope this helps!

 

[Wilkiea]

1) select the entire range of blank cells that you wish to apply data validation to
2) under the data tab choose data validation
3) choose allow > custom
4) under Formula, paste

=MOD(INDIRECT(ADDRESS(ROW(),COLUMN())),INDIRECT(ADDRESS(ROW(),COLUMN()-1)))=0

This will only work assuming the min order qty is immediately to the left of the blank cell... see where it says "COLUMN()-1".. its looking to the cell that is one column to the left (your minimum order qty) to perform the math. If the min order qty was two cells to the left you would change this to -2 and so on.

I tried this and it works - it throws an error if you enter a number that is not a multiple of the min order qty. Under the Error Alert tab you would have a custom message explaining the value has to be a multiple of the min. order qty

 

[berniean]

=MOD(INDIRECT(ADDRESS(ROW(),COLUMN())),INDIRECT(ADDRESS(ROW(),COLUMN()-1)))=0

Works like a charm! Thanks so very much...

 

[berniean]

So now Sales has thrown a monkey wrench into the works and moved the MOQ column to the right of the customer column (instead of to the left as before) on the Order Form. The MOD formula works with -1 if the MOQ column is to the left, but does not work if the MOQ column is to the right of the customer column and I use +1. I understand most of what the formula does, but I don't understand why changing the column reference does not work.

Is there a way to modify this formula so that wherever the customer column is in relation to the MOQ column, right or left, the data validation will work?

As always, any assistance is greatly appreciated!

 

[berniean]

I must have done something wrong the first time I tried. The MOD formula works with the +1 for having the MOQ to the right of the customer column.