Entering a number that is only a multiple of an adjacent cell

[AmyA]

Hi Everyone,

I'm new here and very much a novice when it comes to Excel.

Trying to create an order form in Excel and in some instances, the customers are only allowed to order quantities that are a specific multiple. For instance, some products can only be ordered in multiple of five. While others can be ordered in multiples of 20. How could I create a formula in a particular cell which would 'read' the cell containing the order quantity multiple and only accept entries that actually correspond to that specific quantity? Say the multiple order quantity is five and someone enters a quantity of seven - I'd like some sort of message to appear stating that the entry is not a multiple of five. However, if the entry is indeed five or 20, or 85, or 210, etc, then no message would need to occur since the quantity is correct multiple.

From my very limited knowledge of Excel, it would seem that a formula such as this would utilize the MOD function, but beyond that, I'm stuck.

I've attempted to search for potential solutions prior to posting this question, but I haven't found anything similar to what I'm looking for.

Thanks Everyone!

Cheers, Amy

 

[Micron]

AFAIK, cell formulas won't raise messages but I suppose you could populate a nearby cell with text or use conditional formatting. To get an actual message prompt you'd need to call a vba function. Either way, I think that next to your qty cell you'd need a unit cell and use Mod as you say. That could look like = MOD(B2,C2) in cell D2 where B2=55 and C2 = 5 Then if D2 is not 0, colour the cell.

 

[JvdV]

You could use data validation in your cell. Say your input is in B1, then use the following custom validation rule:

=MOD(B1,$A1)=0

Now B1 would only accept multiples of the value in cell A1. If not, it will notify the user.

 

[AmyA]

You could use data validation in your cell. Say your input is in B1, then use the following custom validation rule:

=MOD(B1,$A1)=0

Now B1 would only accept multiples of the value in cell A1. If not, it will notify the user.

Thank you for your reply. I attempted to use this and modified the cell references to match my worksheet. However, I'm getting a circular reference warning, so I'm obviously not understanding it correctly

In my worksheet, the input is in cell J26 and the value is in cell I26. In cell J26, I enter the following: =MOD(J26,$I26)=0

Cheers, Amy

 

[kweaver]

I would suggest having a (small?) table with product IDs and respective multiples.
Then, a drop-down (my example uses D2) to select the product and another cell to indicate the order qty.
You could use conditional formatting to color or whatever an invalid quantity and another cell with an IF statement to report on the situation.

Book1
	A	B	C	D	E
1	Product	Multiples		Product Ordered	QTY Ordered
2	AAA	2		EEE	24
3	BBB	5
4	CCC	10
5	DDD	5
6	EEE	12
7	FFf	5
Sheet1

E1 is:

=IF(0<>MOD(E2,VLOOKUP(D2,A2:B7,2,FALSE)),"Invalid QTY","")

Product	Multiples		Product Ordered	QTY Ordered
AAA	2​		EEE	10​	Invalid QTY
BBB	5​
CCC	10​
DDD	5​
EEE	12​
FFf	5​

 

[Micron]

Why is 10 not a valid order amount for multiples of 2?

However, I'm getting a circular reference warning, so I'm obviously not understanding it correctly

Your formula cell cannot reference itself. You'd put each input value into separate columns and the formula in a third column as suggested in post 2 where the sample columns were B, C, and D.

 

[Dave Patton]

T202303a.xlsm
	A	B	C	D	E	F
1	Product	Multiples		Product Ordered	QTY Ordered	Formula for Validation
2	AAA	2		EEE	24	OK
3	BBB	5		AAA	10	OK
4	CCC	10		FFF	16	Invalid
5	DDD	5		DDD	100	OK
6	EEE	12		DDD	24	Invalid
7	FFf	5
3a

Cell Formulas
Range	Formula
F2:F6	F2	=IF(MOD(E2,VLOOKUP(D2,$A$2:$B$7,2,0)),"Invalid","OK")

 

[Micron]

Another problem with multiples of 12, ordered 24 and that's not OK?
I'd just use simple Mod function; just make sure it's in its own column. Complicated formulas just seem to be prone to error.

 

[JvdV]

Thank you for your reply. I attempted to use this and modified the cell references to match my worksheet. However, I'm getting a circular reference warning, so I'm obviously not understanding it correctly

In my worksheet, the input is in cell J26 and the value is in cell I26. In cell J26, I enter the following: =MOD(J26,$I26)=0

Cheers, Amy

It worked fine for me, people said the formula needs to be in a third column which is not true. Make sure the 1st parameter is the cell that you wish to have user input, the 2nd parameter is the cell that holds the constant you wish to check the input against. Check the test below.

See this test!

 

[AmyA]

It worked fine for me, people said the formula needs to be in a third column which is not true. Make sure the 1st parameter is the cell that you wish to have user input, the 2nd parameter is the cell that holds the constant you wish to check the input against. Check the test below.

See this test!

Thank you for this, I appreciate it! It works brilliantly and very simple to implement without adding any further columns.