[SOLVED] Inventory System - Not sure ow to achieve this.

Hi All

I'm trying to create a an inventory system for myself and I have no idea how to handle this part.

engraving-records.jpg

The image above is the records I would fill out as I go each day. The Item column is data via a dropdown from my Inventory sheet. Shown Below (Column B)


Inventory.jpg

Basically I want it so the value of the 'Qty' Column in the Engraving Record sheet is added to the 'Number Sold' Column in the Inventory Sheet and taken off the "Quantity in Stock" Column

Obviously The Item on the Engraved sheet (column D) also has to Match the Item on the Inventory sheet (Column B) as well.

I have no idea how to start going about how to achieve this.

Any Help or guidance would be much apricated.

Kind Regards,

John

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

On the Inventory (After) sheet try the following:
1. Paste the following into cell D3 and then drag the fill handle down to cell D10:

Formula:

Please Login or Register  to view this content.

2. Paste the following into cell I3 and then drag the fill handle down to cell I10:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

I feel that the best option is to convert the range A2:E7 on the Engraving Record sheet into an Excel table.
When a new row is added to the table by selecting cell E7 and then pressing the Tab key, the formula for column I on the Inventory (After) sheet will update to include the new row.
Let us know if you have any questions.

Looking at the notes on the Inventory (After) sheet I am struggling to understand where/how the formula in post #7 should be applied.
Perhaps uploading another Excel file would help clear things up.
Please remember to manually show us the expected results of the new column.
One comment, if you give the tables meaningful names such as InventoryTable and EngravingTable it will be helpful when/if formulas need troubleshooting.
Let us know if you have any questions.

(From worksheet) The "Quantity in Stock" Column Should ideally be one column.

In order to have only one column I feel that there will have to be a separate record of the initial stock as well as the stock that is added by reorder as modeled on the Restock sheet.
The formula for the Quantity in Stock column on the Inventory sheet could be: =SUMIFS(TAB_Restock[Quantity in Stock],TAB_Restock[Item Code (SKU)],[@[Item Code (SKU)]])-[@[Number Sold]]
Let us know if you have any questions.