Stock Inventory Control Help

stottle

New Member
Joined
Mar 17, 2004
Messages
26
I am trying to create a spreadsheet to keep tabs of all our printer cartridges at work and have succeeded in using vlookups to show the data I want on the order form. The problem I have is when I want to order some more cartridges. Is there a macro I can write or a formula I can use that will keep a running total for me.

I have the two worksheets (itinerary and order) and when the order is made I want it to add the quantity ordered to the current stock levels on the itinerary sheet.

From that I’m sure I will be able to work out when users take the cartridges from the stock.
Printer Stocktest.xls
ABCDEFGHIJKL
1PoductCodeDescriptionMan.CodeAmountTypeOfUnitUnitofMeasureBestPriceSupplierCodeSupplierCurentStocklastorderNewStock
2B001BrotherFax-8250pDrumDR-2001Faxeach78.501003IanSmiths112
3B002BrotherFax-8250pTonerTN-2001Faxeach17.001001Danwood224
4C001CanonBJC-1000BC-051Printereach25.871001Danwood3811
5C002CanonBJC-250BC-051Printereach25.871001Danwood4812
6H001DeskJet3820BlackC6615D2Printereach15.001003IanSmiths41216
7H002DeskJet3820ColourC6578A2Printereach33.801003IanSmiths448
8H003DeskJet3822BlackC6615D4Printereach15.001003IanSmiths448
9H004DeskJet3822ColourC6578A4Printereach33.801003IanSmiths448
10H005DeskJet5650BlackC6656A1Printereach12.401003IanSmiths448
11H006DeskJet5650ColourC6657A1Printereach18.531003IanSmiths448
Itinerary



[/url]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I forgot to say that the K column on the itinerary worksheet sheet is rubbish, thats was me trying to get something to work, dunno what though?
 
Upvote 0
A low-tech option --
Book1
ABCDEFGH
1DispensedOrdered
2CodeQtyOnHandCodeQtyDateCodeQtyRec'dDateRec'd
3B0012B00131/1/2004B001251/11/2004
4B00297B00231/6/2004B0021001/25/2004
5B001201/8/2004
6
Sheet1


B3: =SUMPRODUCT(--($F$3:$F$100=$A3),--($H$3:$H$100<=TODAY()),$G$3:$G$100)-SUMPRODUCT(--($C$3:$C$100=$A3),--($E$3:$E$100<=TODAY()),$D$3:$D$100)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top