Problem linking sheets to update stock values

[XBookNoob]

I have two sheets that refer to items, their item codes and their quantities. Sheet1 has all the items listed with columns: Item, Code, Current stock level. Sheet2 is used as a basic order history sheet with columns: name, item, item code, quantity ordered, date, issued (A yes/no value).

Is it possible to input the details of an order in Sheet2 and have excel search for the item code on sheet1 and then subtract the order quantity I input from the stock value? Can this be done using an IF function?

I know this is not a real formula but it might explain better what I need:

=IF 'Y' in cell X then search tab1 cell range X:X for 'code' row, then subtract tab2 cell quantity from tab1 stock quantity

I am currently doing this manually between the two sheets.

Apologies if this a simple problem to solve, but if you could give me an example of how to do this or point me to a previous thread, but I would be very grateful and maybe, just maybe, you will reply in time to stop me making a new head shaped window in my bedroom wall.

Thank you

[FoxSeaLady]

Hi

To automate the closing stock level you will need to input further information
1. The opening stock level at the start
2. Purchases added to stock

The opening stock can be added as a new column on sheet 1 and the purchases added in the same manner as issues on sheet 2.

I have developed a basic model on what I guess you have at the moment but you will see the principal.
Then using2 sumif formula in the 2 additional helper columns on sheet 1 and a simple opening stock +purchases-issues in the current stock col will give you a solution.
The 2 sumif formula simply total purchases and total issues. The formula are:-

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I have attached my spreadsheet so you can see what I have done. Without an sample it is difficult to present a layout to suit your needs.

Cheers

[karmapala]

I have two sheets that refer to items, their item codes and their quantities. Sheet1 has all the items listed with columns: Item, Code, Current stock level. Sheet2 is used as a basic order history sheet with columns: name, item, item code, quantity ordered, date, issued (A yes/no value).

Is it possible to input the details of an order in Sheet2 and have excel search for the item code on sheet1 and then subtract the order quantity I input from the stock value? Can this be done using an IF function?

I know this is not a real formula but it might explain better what I need:

=IF 'Y' in cell X then search tab1 cell range X:X for 'code' row, then subtract tab2 cell quantity from tab1 stock quantity

I am currently doing this manually between the two sheets.

Apologies if this a simple problem to solve, but if you could give me an example of how to do this or point me to a previous thread, but I would be very grateful and maybe, just maybe, you will reply in time to stop me making a new head shaped window in my bedroom wall.

Thank you

Your situation is similar with me.

I do that by using 3 sheets.
Sheet1 has all the data of stock in/out
Sheet2 is the data where the stock is issued.
I have a macro button in this Sheet2 which when after click,
the information of an issued stock in Sheet2 is copied to Sheet1.
Sheet3 is a PivotTable from the Data in Sheet1.

So my way, basically is :
Sheet1 contains the stock value (plus) and the stock value (minus/purchased),
with a header : Date, Code, Item, QTY

Sheet2 has the same header, but I use 6 columns
where the fourth column I put a formula =-E2 (and I hide the column)
the fifth column is where the user type the order qty
and the sixth colums is yes/no issued.

At first the macro prompt the user to choose the cell which has value "Yes" (for example, cell F3)
Then the macro copy the cell from A3 to D3, paste it to the last blank row in column A of Sheet1.
Then the macro refresh the Pivot Table, then select Sheet3.

Maybe you can implement it with your situation.
Please have a look at the attachment.