Excel Data Validation Based on Another Cell

In the image below we select a code in cell D2 and then enter a quantity in cell E2.

The E2 quantity needs to be a multiple of the carton quantity (cell F2) for the code selected.

In the example above 150 in cell E2 would be invalid and 160 would be valid.

The Custom option in Data Validation enables the use of a formula to handle the validation.

The formula in the above image is.

=AND(ISNUMBER(E2),MOD(E2,F2)=0,E2>0)

The ISNUMBER function ensures a number is entered.

The MOD function return the remainder after a division. If the remainder (the MOD result) is zero it means the number divides evenly into the other number.

The number also need to be greater than zero.

I have included an error message as shown in the image below.

This displays the following dialog for invalid entries.