inventory stock and consumed

**hyderabadi** · 06-24-2011, 06:48 AM

hi

i am maintaining an inventory which has all the stock in sheet1 and the consumed or u may say removal is maintained in sheet2 total monthly receipts and consumption of material is on sheet3.

my question here is can anyone make the formula that sheet2 consumption deducts the stock from sheet1 and adds up as consumption in sheet3,

my sheet becomes like that of tally9 can anyone help on this issue please.

**p24leclerc** · 07-08-2011, 10:36 PM

a sample file attached would help us to create your formula based on your file structure.
Also, how would you input new inventory because I'm sure you have to replenish your stocks, don't you? You have to take this into account in sheet 1 formula.

**hyderabadi** · 07-15-2011, 12:39 AM

HI P24

I am attatching my excel inventory sample hope you update the formula on the same sheet hope to get a formula

**Mordred** · 07-15-2011, 12:43 AM

There has been nothing attached to this thread.

**Mordred** · 07-15-2011, 01:32 AM

Hi hyderabadi, You'll have to amend the code within the following workbook to suit your ranges to be altered. This book also relies on the fact that your sheet layouts would be the same. Anyhow, give it a go!

**hyderabadi** · 07-15-2011, 05:58 AM

hi i attatched my inventory sample please update the formula

**hyderabadi** · 07-26-2011, 04:13 AM

any help would be appreciated

**hyderabadi** · 07-27-2011, 06:26 AM

suggetions are welcome please

**p24leclerc** · 07-27-2011, 05:38 PM

The problem in your sheet is that you record consumption in sheet2 (Issuance) by date. This means that you have more than one row with the same product number. It would be impossible to keep stock in sheet1 and consumption in sheet3 up to date with a simple formula. You should use a specific sheet to enter the issues and run a macro to transfer these issues to the proper sheets. More like Mordred suggested in his file ProductCounts.xls

You should start with his macro and modify it to suit your needs.