Very stuck trying to create a stock controll system

[zilch4ry]

Hi all,

Right.. I am creating a spreadsheet for an ICT task which uses a stock control system, based on which orders the customer has made, for example if the customer orders a "Mozzarella and Tomato pizza" the spreadsheet must deduct 1 mozzarella cheese and 1 tomato from a starting amount; I chose to use 100 as the starting amount.

The problem I have is that I am not too great on excel, meaning I've tried everything and ended up using IF and countIF functions, these do the job fine, but as soon as this customer ends his order and the spreadsheet information is all reset to 0 the raw stock does not stay at its new stock level and raises back to 100 because there are no information in the cells.

I was just wondering is anybody knows a better way of recording stock as I'm getting pretty stuck.

Thanks,

Tom

 

[mancemonster]

Hi there,

Simple solution... Copy your closing stock values.

highlight your opening stock values, Right click, select paste special then values.

If you then remove the transaction you will be left with your opening stock carried forward, ready for new transactions.

Alternatively link your opening stock value, back to your last sheet. So that the closing stock of one sheet equals the opening stock of the next.

Hope this helps!

 

[zilch4ry]

Thank you very much for the help.