How To Use Data Validation To Control Inventory Distribution In Excel

In this tutorial we will use the AND function along with SUM and MOD in Data Validation to control the distribution of inventory to various locations.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

So here I have an area with six inventory items, the amount of each in inventory, and the five locations I want to distribute that inventory to:

I have two conditions that must be met:

And here is the formula we will enter into Data Validation to meet those conditions:

=AND(SUM($C2:$G2)< =$B2,MOD(C2,1)=0)

So, I’ll highlight the area I want to control with Data Validation, choose “Custom” for the type of Data Validation, and enter the formula in the Formula area:

The AND function requires that both arguments be true. For cell C2, the arguments are:

1) SUM($C2:$G2)< =$B2
2) MOD(C2,1)=0

Notice in the first argument, we’ve locked the column letters but not the row numbers, so the condition will carry down to all the rows. The condition is that the sum of the values for the row can’t exceed the inventory in that row in column B.

In the second argument, we used the MOD function. This function has two arguments – number and divisor. The number is whatever we enter, and the divisor is one. The condition says that after you divide the number by the divisor, the remainder must be zero, or no remainder. Meaning that it is a whole number. So, in order for this argument to be met, it must be a whole number.

Take a few minutes to watch the video and see how the process works. I’m sure you’ll find it useful in various aspects of your Excel work.


What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply