Stock/Inventory Control - Running Total

Hi there!

I am trying to create an all-in-one excel spreadsheet for my inventory control. I have gotten as far as creating a master stock list including a code, description, cost, and quantity booked into the system originally.

I am now working on a second sheet on which to keep a running total of all good incoming and outgoing using a vlookup to pull the information based on a code. The last thing I need to get down pat if a way to alter the running total "On Hand" column to not just take the value of stock originally booked and subtract the amount booked out each time. It is difficult for me to explain, but I am hoping someone can take a look at the attached file and offer some tips/a solution?

Thanks in advance!

Inventory Control Example.xls

Sorry, Sheet1 is my master stock list where Sheet2 grabs the item information from. Sheet2 is a running total for both incoming and outgoing stock. I didn't explain that very well.

I would like the incoming/outgoing figures entered on Sheet2 to affect the On Hand value in Sheet1, however every time I enter a value, it calculates the original On Hand value minus the value entered on the new line, regardless of any previous entries.

Every line on Sheet2 is one order, come billing time I can filter for specific jobs to grab all items used against the job number listed in column B.

Originally Posted by humdingaling

ok sheet 1 master...then quantity booked should be a formula instead of fixed value and it would be a total booked?

then you manually type in incoming and outgoing on sheet 2 then?

Quantity Booked is probably a misleading heading. It is the base amount I have purchased for stock and should also be the value I restock to. I figured it would be easier to start with a static number to work off, but if that isn't the case, I could perhaps enter all of my starting stock into the incoming column of Sheet2?

And yes, incoming and outgoing values on Sheet2 are manually entered along with the job number and date.

That is perfect! Thank you so much.

When you say it needs to be extended when the list grows, do you mean the master stock list or the running list on Sheet2?

Also, sorry for the stupid question, extended how?

Very helpful, thanks.