Reducing Quantity When an Item is Sold

[Guest]

I am struggling to find what I think should be a very simple formula to
automatically reduce the Quantity when an item is sold.

I have several columns with formulas to calculate different tasks:

Retail Price Our Cost Net Quantity Sold Total COG
Inventory Amt.
$18.00 $7.00 $11.00 4 1 $18.00
$7.00 $28.00

What I would like to do is when I sell an item and mark the amount Sold, it
automatically deducts from the Quantity that I have in stock. So by marking
1 in the Sold Column, it would make the Quantity 3. I have been doing this
manually because I haven't figured out a formula to make this work. Any
help would be greatly appreciated. Thanks.

 

[Sandy Mann]

Tami,

Assuming that your data is in A1:B8 then you can use that Evant Macro.
Right-click on the sheet tab and select "View Code" and then copy and paste:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 5 Then Exit Sub
If Target.Row <> 2 Then Exit Sub

Cells(2, 4).Value = Cells(2, 4).Value - Target.Value

End Sub



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

You could try something like this. Add another column next to 'Sold' called
'Restocked' (Thus Sold is in column E and restocked is column F)
Put this formula in the Quantity column. =F2-E2
Then put the intial quantity number in the restocked column. After that
when you sell items and enter a number in sold it will reduce the quantity.
when you add items to your inventory in the restocked column it will increase
your quantity. (I startered the formula in row 2 assuming that headings are
in row 1.)

 

[Guest]

Sold item in column E
Quantity in column D
In D2: =4-E2

 

[Guest]

This worked. Thank you!

You could try something like this. Add another column next to 'Sold' called
'Restocked' (Thus Sold is in column E and restocked is column F)
Put this formula in the Quantity column. =F2-E2
Then put the intial quantity number in the restocked column. After that
when you sell items and enter a number in sold it will reduce the quantity.
when you add items to your inventory in the restocked column it will increase
your quantity. (I startered the formula in row 2 assuming that headings are
in row 1.)