View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Roger Govier[_3_] Roger Govier[_3_] is offline
external usenet poster
 
Posts: 2,480
Default Excel simple stock control

Hi Tony

There are many ways to achieve this without VBA.

On row 3 of your sheet enter Part No, Desc, Qty In, Qty Out
Make your entries going down the sheet, entering values in column C or D
dependent upon whether it was In or Out. Use positive numbers in all cases.
In C2 enter
=SUBTOTAL(9,C4:C1000)
copy across to D2
In C1 Enter Balance and in D1 enter
=C2-D2

Mark row 3Data FilterAutofilter
Use the dropdown on Column A to select any part number and you will not only
see the balance in D1, but also, all of the transactions in and out for that
part as an audit trail.

Using VBA in the manner you described, can be down but you would be left
with no record of what had come in or gone out, and if there were any "foul
up", no way of correcting.

--
Regards
Roger Govier

"TonyB" wrote in message
...
I am a service engineer, and i'm looking to use excel to manage my stock
better

I have tried a few things and it's all manually input/edited

I would like to know if it's possibale to use VB, to auto manage these
levels
IE, 1 box asks for the part number
2nd asks you how many
3rd/4th boxes are "+", "-"

When + or - is pressed the part number will automatically add's/subtracts
that part level.

Say.. I had 10x abcde's I enter 2 in "How Many", then press "-" it will
then
adjust the level from 10 to 8

My excel sheet would have parts in it
Col1------Col2------Col 3
Part No---Desc------Qty
ABCDE----Bits-------10

Can any one please help

Thanks in advise for anyones help

Tony