Inventory Control

[Guest]

I am trying to create an inventory system that tracks products by Lot Number.
I currently have 2 worksheets: one with inventory balances and one
transaction page. I would like to be able to only input inventory changes on
the transaction page and be able to see the updated balances on the balance
sheet pages. The balance changes would correspond to item Lot Number so that
when the lot number is typed into the transaction page, the balance page will
be updated with the amount added or removed.

I know this can be done because I've done it before, but it's been so long
since I've had to start from scratch. Can someone help me out?

Thanks!

 

[Guest]

You could put a formula on your balance page
=sumif(transaction page lot number column,inventory balance lot number,
transaction amount column). You could do this in a separate column and then
have one column for beginning balance

if you also need to consider part # make a column that joins lot number and
part number (lotnumber&partnumber) do this on both sheets then apply the same
reasoning as above

 

[Guest]

Thank you so much! You saved my sanity!

You could put a formula on your balance page
=sumif(transaction page lot number column,inventory balance lot number,
transaction amount column). You could do this in a separate column and then
have one column for beginning balance

if you also need to consider part # make a column that joins lot number and
part number (lotnumber&partnumber) do this on both sheets then apply the same
reasoning as above