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

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.