Automatic quantity addition and subtraction based on values entered in a different Excel sheet.

[Cohen290]

Hey everyone, 

So here is the dream. I am creating an inventory sheet that is accompanied by a receiving sheet and a sales sheet. On inventory sheet I have given a sequential unique number to each item as an identifier and in the receiving and sales sheet I have added data validation drop down to select items from the inventory sheet to be sold and received and I have Vlookups set up to automatically fill out certain columns on the receiving sheet and sales sheet based on the values in the inventory sheet. 

I would like to have it so that when a quantity is added to the corresponding quantity column in the sales or receiving sheet it will automatically add or subtract the quantity related to the corresponding ID number on the inventory sheet.

I am wondering if maybe there is a formula, I can put in the quantity column on the inventory sheet so that this is possible. 

Below are screenshots for reference: 

Inventory SheetSales sheetReceiving Sheet

[#2 · Accepted solution]

@Cohen290 

To automatically update the quantity in your inventory sheet when you add or subtract from the sales or receiving sheets, you can use Excel's built-in functions like SUMIFS or INDEX/MATCH.

I cot not see clearly your pictures thats why this example. 

Here's a general guideline on how to set it up:

Assuming you have three sheets:

1. Inventory Sheet: Where you maintain your inventory with unique IDs, descriptions, and quantities.
2. Receiving Sheet: Where you record items received.
3. Sales Sheet: Where you record items sold.

In the Inventory Sheet:

1. Let's say your unique item IDs are in column A, descriptions in column B, and initial quantities in column C (e.g., C2 for the first item).
2. In column D (e.g., D2), use a formula to calculate the updated quantity based on items received and sold. This formula adds the quantities from the Receiving Sheet and subtracts the quantities from the Sales Sheet:

=C2 + SUMIFS('Receiving Sheet'!QuantityColumn, 'Receiving Sheet'!ItemIDColumn, A2) - SUMIFS('Sales Sheet'!QuantityColumn, 'Sales Sheet'!ItemIDColumn, A2)

Or…

=LET(

   ItemID, A2,

   InitialQty, C2,

   ReceivedQty, SUMIFS('Receiving Sheet'!QuantityColumn, 'Receiving Sheet'!ItemIDColumn, ItemID),

   SoldQty, SUMIFS('Sales Sheet'!QuantityColumn, 'Sales Sheet'!ItemIDColumn, ItemID),

   NewQty, InitialQty + ReceivedQty - SoldQty,

   NewQty

)

1. Drag this formula down for all items in your inventory.

In the Receiving and Sales Sheets:

1. When you add an entry in the Receiving or Sales Sheet, ensure that the unique item ID is selected from the dropdown.
2. Then, add the quantity in the relevant column (e.g., QuantityColumn).
3. The inventory sheet will automatically update its quantity for that item based on the formula.

This setup will allow you to track inventory changes as you receive or sell items. It's important to ensure that your item IDs match between sheets, and this method provides you with real-time tracking of inventory.

Keep in mind that Excel's calculations work in real-time, so any change in the Receiving or Sales Sheet will immediately update the Inventory Sheet. The text, steps and Code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

Awesome, thank you for the response.

[#4]

Hi NikolinoDE! 

I have a similar question and for the life of me can't figure out how to adjust your formula (or even if it's possible here) to work with my sheets. We have an Inventory list with columns for Brand, Product, Qty then the multiple prices columns. We have a Sales In & Out sheet with columns for the Date, Product (which uses Data Validation for a drop down list which pulls up all of the products from the Products column in the Inventory Sheet), Type, Price, Qty, then Total Cost. When an item is sold it is logged in the Sales In & Out sheet but we then have to go into the Inventory sheet and update the Qty there as well. Is it possible to have the Qty in the Inventory automatically update when we log an item sold with the qty in the Sales In & Out sheet??