# Limit order quantity to multiple of minimum order quantity

#### berniean

##### New Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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!

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

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

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

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!

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.

Replies
9
Views
179
Replies
0
Views
278
Replies
1
Views
111
Replies
22
Views
696
Replies
1
Views
54

1,196,073
Messages
6,013,270
Members
441,759
Latest member
ab_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back