Excel simple stock control

  • Thread starter Thread starter TonyB
  • Start date Start date
T

TonyB

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
 
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 3>Data Filter>Autofilter
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.
 
Back
Top