By setting up data validation to limit the sales qty entry within the available stock qty, we can fix one of the reasons for negative inventory in Google Sheets.
It will help you avoid raising sales invoices for products you do not have in stock for customers.
Let’s say that the available stock of item 1 is 100, and customers A and B place orders of 60 and 50, respectively.
If you enter the sales orders as it is, it may lead to negative inventory.
We want to limit the second-order quantity within the stock quantity, i.e., 40 from 50, so the total will be 100 (60+40).
When you have multiple products and orders, it may not be easy to handle this scenario manually. You can use a data validation rule for this in Google Sheets.
Data Validation to Limit Sales Qty Within the Stock Qty in Google Sheets
Assume we are a trader who purchases aggregate materials from crushers and sells the same to different customers.
We have two tables in Google Sheets; one is for purchases (stock_table), and the other is for sales (sales_table).
All our aggregate purchases go to the stock_table.
We want to set up the data validation in sales_table to limit sales entry within the stock_table quantity.
Please see the tables below. In them, I’ll only include the data required to test the data validation to limit the sales quantity within the stock quantity.
Table 1 (Stock_Table)
Note:- The stock table may contain single or multiple purchases of the same items. But the data validation rule in Sales_Table won’t have any changes concerning it.
Example of Single Entry of the Same Item in the Stock Table:
Example of Multiple Entries of the Same Item in the Stock Table:
Table 2 (Sales_Table)
The available quantity of the item code “RB01,” i.e., Road Base, as per the stock_table_1, is 140 MT, and the stock_table_2 is 495 MT.
If we consider the total stock of Road Base material is 140 MT, then the data validation rule must restrict the entry of any quantity >140 in the sales_table_1. This way, we can prevent negative inventory.
So, in cell D4, the value entered must be limited to 40 MT.
Data Validation to Limit or Restrict Sales Qty Entry Within the Stock Qty
We can use the following data validation rule in Google Sheets to limit the sales quantity entry in the sales table and thus ensure that it is within the stock table quantity.
=sumif($A$3:$A,A3,$D$3:$D)<=sumif(Sheet1!$A$3:$A,A3,Sheet1!$D$3:$D)To apply this rule, please follow the below steps as per the new sidebar data validation panel in Google Sheets.
- Select D3:D in sales_table_1.
- Go to the Data menu and select Data Validation.
- Select “Add Rule.”
- Under “Criteria,” select “Custom formula is.”
- Copy-paste the above rule.
- What do you want if the data is invalid? I prefer to reject the input. So select it.
- Select “Done.”
Formula Logic
As you can see, two SUMIF formulas are the core of the data validation rule to restrict or limit sales quantity entry and thus ensure that it always falls within the stock quantity.
Do you know the logic behind this data validation rule?
It’s simple. If the sales table quantity (SUMIF_1) is less than or equal to the stock table quantity (SUMIF_2), validate the entry, else invalidate.
The condition in both the SUMIF is the string (item) in A3.
Based on it, the first SUMIF summarizes the sales table, whereas the second SUMIF summarizes the stock table.
Since A3 is a relative reference, in data validation, it will change to A4, A5, and A6… in corresponding rows.
Related: How to Limit a Column Total Within a Specific Value.
