Hi There,
I hope someone can help.
I have attached a spreadsheet that has a few formulas.
Explanation of sheets:
"Daily Transactions" is the sheet used that will be filled in daily with stock in and out. My focus on this sheet is column F. (will explain soon)
"Warehouse 1" & "Warehouse 2" is linked to "Daily Transactions" and will automatically deduct and add stock as they are filled in on the "Daily Transactions Sheet"
There is a formula in column J & K that is linked to the "Daily Transaction Sheet"
My question is, how do I add another criteria in the SUMIF function of column J & K to be able to not only lookup the product, but also Column F on the "Daily Transaction" sheet.
I want the formula to be able to see which product has been dispatched/received, and also from which warehouse it has been taken, so that it will deduct the stock from the correct warehouse.
I had something in mind to replace the reference to B6 in this formula of column J with an IF function to say lookup the product, if the warehouse is "Warehouse 1" and on the other sheet, lookup the product if warehouse is "Warehouse 2"
=C6+SUMIF('Daily Transactions'!G:G,B6,'Daily Transactions'!J:J)
And I would likely have to put the same criteria in the formula in column K, because the two column's work together to give me my current stock values.
If anyone can assist me with this, I would really appreciate it.
Currently on my main workable sheet, I have a "Daily Transaction" sheet for each Warehouse and it get confusing work on multiple sheets.
Please ignore the yellow cells that show's #N/A, they are just there for my stock controller to make sure he spells the product names correctly, so that the other sheets can add and subtract the products.
I keep most of the sheet locked and only allow certain fields to be filled in.
Thanks in Advance :-)
Gisela
Bookmarks