Make invoice take items off stocklist in excel workbook

[Guest]

Hi all,
I have used sheet 1 of my workbook to enter stock details, stock no price
etc. Have used Vlookup function in Invoice on Sheet 2 of the workbook to
automate the invoice to bring in description and price from sheet 1. I would
like the invoice on sheet 2 to then take items out of amount of stock on hand
on the stocklist sheet as invoices are completed.
On the invoice the Quantity column is column 1 of sheet 2 A11:A17, stock
number is column 2 B11:B17, Sheet 1 stocklist is A2:n240, stock numbers are
in column 1 and the units sold column is 13 (M) and the total in stock column
is column 12 (L).
I thought maybe using Vlookup but with somehow making it add to units sold
column and then a formula within the total stock column and units sold column
to adjust the figures.
Can anyone help with this?
Thanks
Kathy

 

[Roger Govier]

Hi Kathy

I don't think you can do this with formulae, you would require a macro
to do this.

I am in the process of developing a similar system for a client and for
what its worth, the following are my views on handling stock control,
which you may want to consider as an alternative to what you are proposing.

In general I am averse to just amending Total Sold and Total in Stock on
the fly, as then there is no audit trail and if anything goes wrong it
is impossible to reconstruct what the true position should be. What I am
doing is writing the transaction out to a separate Sheet, called Stock
with Date, Item, Quantity (as the negative of the quantity sold from
the invoice). I am also capturing other information specific to the
clients requirement, but to answer your query the above would suffice.
A simple macro something like

Sub test1()
Dim row As Long
Dim i As Integer

Worksheets("Stock").Select

row = Range("A1").CurrentRegion.Rows.count
For i = 11 To 17

row = row + 1
If Range("Sheet1!A" & i).Value = "" Then Exit Sub

Cells(row, 1) = Range("Sheet1!A" & i).Value
Cells(row, 2) = Range("Sheet1!B" & i).Value
Cells(row, 3) = Range("Sheet1!C" & i).Value * -1
Next

End Sub

You would need to create a blank sheet in your Workbook and name it Stock.
In cell A1 enter Count and in Cell C1 enter =SUBTOTAL(9,$A$2:$A$10000)
In A2 enter Date, B2 enter Item, C2 enter Quantity
The above macro would look through lines 11 to 17 on your invoice, and
for each line it would extract the relevant information.

You would need to run this macro each time you create a new invoice.

If you enter your purchases onto this stock sheet with the numbers in
column C being positive values, then the quantity in stock at any time
could be derived using a SUMPRODUCT formula.

In cell L2 of your Sheet2 enter
=SUMPRODUCT(--(Stock!$B$2:$B$10000=A2),Stock!$C2:$C$10000)
In cell M2 of Sheet2 enter

=SUMPRODUCT(--(Stock!$B$2:$B$10000=A2),--(Stock!$C$2:$C$10000<0),Stock!$C2:$C$10000)
to give the Total Sales.
Copy these formulae down through the rest of Sheet 2

If, on the Stock Sheet, you mark your block of data and choose
Data>Filter>Autofilter you will have a series of dropdown arrows.
If you select the dropdown on column B and select any Stock Item, then
the figure in cell C1 would be the net stock figure and the visible rows
would show you all of the purchases and sales making up that total.

Obviously, to start the system off you would need to enter onto the
Stock sheet a line for each item as if it were a purchase, with the
current quantity in stock as the quantity.

If you require more help, post back.

Regards

Roger Govier

 

[Guest]

Hi Roger,
Thanks very much, what you are saying makes sense. I'll give it a try and
let you know how I go it's a bit hard to tell when just reading it.
Thanks again,
cheers,
Kathy

 

[Roger Govier]

Hi Kathy

If you get stuck, you can send a copy of your file direct to me and I
will see if I can help you.
Remove NOSPAM from my email address to post direct.

Regards

Roger Govier