inventory help!!! I need to get some sleep...
Posted by suzanne on December 16, 2001 12:56 AM
i manage a store, we have over 350 items, currently i have kept items title on column A, C and E, with quantity in stock on column B, D and F. Every night i have to update stock which I do by locating product and then manually (using a calculator) i deduct quantity sold (or add quantity bought) and then re-enter the final result to that cell. Is there a easier way, where i can double click on the product name and a box comes up with the product name and current stock for that product, and a input area to receive data entered by me, which will either subtract or add to the cell value and then store it in that cell....please help...I have to work way over 2-3 hours to to this tedious task.. or if you have a better solution...
Posted by denis on December 16, 2001 6:14 AM
Yes it can be done easily, I noticed you said you use a calculator to find out how much is felt in stock. A simple formula say column B opening stock, column D is stock sold & column F is stock bought. Then goto cell g1 and put this formula less the apostraphe's "+b1-d1+f1" , this will give you current stock instore.
Posted by Rick E on December 16, 2001 6:19 AM
Yes easy request, Email me your workbook and I'll tell what it will take, or just email me so we can discuss the problem.
Posted by Tom Dickinson on December 16, 2001 9:56 AM
Suzanne:
Try this:
Sub QuantityChange()
ActiveCell.Offset(, 1) = InputBox(ActiveCell & " " & ActiveCell.Offset(, 1), "Change Amount", 0) + ActiveCell.Offset(, 1)
End Sub
The first and last lines are by themselves, the other 3 lines should be entered as 1 long line.
I would suggest putting a button on the spreadsheet in a handy place. You may want to put several buttons on the sheet, all starting the same macro.
To run the macro, highlight the ITEM you want to change, then press the macro button. At the prompt, enter the amount you are adding or subtracting (use a minus sign to subtract).
Hope this helps, and catch some ZZZs.
Posted by Tom Dickinson on December 16, 2001 10:00 AM
Don,t get confused
The macro doesn't appear the same in the comments box (5 lines of code) as it does on the board (3 lines of code). The 3 lines is correct
