Limit order quantity to multiple of minimum order quantity

Thanks:  0
Likes:  0

# Thread: Limit order quantity to multiple of minimum order quantity

1. ## Limit order quantity to multiple of minimum order quantity

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.

2. ## Re: Limit order quantity to multiple of minimum order quantity

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!

3. ## Re: Limit order quantity to multiple of minimum order quantity

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

4. ## Re: Limit order quantity to multiple of minimum order quantity

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

5. ## Re: Limit order quantity to multiple of minimum order quantity

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!

6. ## Re: Limit order quantity to multiple of minimum order quantity

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•