Results 1 to 6 of 6

Limit order quantity to multiple of minimum order quantity

This is a discussion on Limit order quantity to multiple of minimum order quantity within the Excel Questions forums, part of the Question Forums category; I have a price list with Minimum Order Quantity in one column and a blank cell in the next column ...

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    5

    Default 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. #2
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,087

    Default 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!
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  3. #3
    New Member
    Join Date
    Apr 2013
    Posts
    1

    Default 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

    =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

  4. #4
    New Member
    Join Date
    Nov 2009
    Posts
    5

    Default Re: Limit order quantity to multiple of minimum order quantity

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

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

  5. #5
    New Member
    Join Date
    Nov 2009
    Posts
    5

    Default 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. #6
    New Member
    Join Date
    Nov 2009
    Posts
    5

    Default 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.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com