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.

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


with @tiscali.co.uk


"Tami" wrote in message
...
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.

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.)

"Tami" wrote:

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.

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

"Tami" wrote:

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.

This worked. Thank you!

"tim m" wrote:

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.)

"Tami" wrote:

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.