Excel Stock Inventory/Sales Workbook

Hi all,

I have a workbook with 2 worksheets - Sales and Inventory (attached an example). The sales sheet documents all sales made by my company, with cost and quantity of the items sold, and the inventory includes how many of each we have in stock. I want this inventory to automatically update with the sales.

Example:

In the example workbook attached, we are selling tennis balls, footballs and rugby balls (ignore the cost) - we have 2 separate sales, each with different quantities for the different balls. On the inventory page, BEFORE taking those sales in to account, we have 10 in stock of each item.

I want the inventory to automatically update to show that we only have 7 in stock on the inventory page for each item (given we sold 3 of each from 2 sales) - any help please? We do have 3 digit product codes for our products if it helps to add a column in with numbers instead.

Best,
John

Okay, that works plugging it in to the function, works great. However, I need to understand how this works (as my real inventory isn't as simple as this)...I've searched up the various parts of the function, and while I understand some of the reasons why you use the different parts, still pretty confusing

What does the LEN and the MID mean and why do we use it?

I understand this, unfortunately not being able to transform this function in to my actual sales spreadsheet with any function.

This is the exact document with confidential info taken out.

Instead of product name I am trying to use 3 digit product SKU to control inventory

Thanks cbatrody - works great now! Didn't realise it was something so simple, had tried many variations but glad to get there! THanks again!