How to Update Stock quantity if I sold items?

**saad3000** · 12-07-2007, 07:02 PM

Hi all, please I want to update my Stock Quantity if I sold Item X or Y or Z and added the quantity of the item sold.

I attached my excel sheet, how could be a possible easy way to automatically update the stock quantity if i changed the sold items quantity?

I have A2:A6 as a Drop down List . Items are "Batteries" <== name list

P.S The Quantity of each battery name is under Stock beside the name of each battery.

Thank you!

**saad3000** · 12-09-2007, 07:07 PM

please anyone knows how to do this?

**Leith Ross** · 12-09-2007, 07:20 PM

Hello Saad3000,

The attached workbook contains the following macros. The main macro adjusts the stock level based on the current stock less what has been sold. The other macro is in the Worksheet_Change() event which checks the entry and then calls the main macro.

Stock Level (main macro)

Please Login or Register  to view this content.

Worksheet_Change() Event Macro

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**saad3000** · 12-09-2007, 07:38 PM

Thanks Leith , I did not thought that it need macros to do this Thats hard! But thanks! appreciate it!

I tried to copy and paste it to my original sheet and i changed the name Set Rng = Worksheets("Sheet1").Range("Batteries") to Sheet2 where my work is.
and I changed also If Not Intersect(Target, Range("B2:B6")) to "D2:D25" in Worksheet_Change() but did not work any ideas?

**Leith Ross** · 12-09-2007, 08:17 PM

Hello Saad3000,

It is possible to do this with formulae but you would need to change your format. The macros are faster since the only run when needed. Every formula in the workbook will be run anytime a change is made to cell that contains a formula. While the formula is often easier, it isn't always the best approach. If you have questions about the code or what it does, just ask.

Sincerely,
Leith Ross

**saad3000** · 12-09-2007, 08:27 PM

I tried to copy and paste it to my original sheet and i changed the name ("Sheet1") to Sheet2 where my work is.
and I changed also Target, Range("B2:B6")) to "D2:D25" in Worksheet_Change() but did not work any ideas?

Function StockLevel(Item_Name As String, Qty_Sold As Double) are these the names of the cells ?

**And if it would be done with a simple formula i will take my chances

**Leith Ross** · 12-10-2007, 12:54 PM

Hello Saad3000,

(This is in reference to a private message)You reversed your macros when you installed them. Your changes to the new addresses were correct. The updates occur automatically whenever you change the sold quantity. No formulas needed. If you have any other questions, let me know.

Sincerely,
Leith Ross