UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Advice On The Best Way To Achieve Stock Level, Office 2013    
 
   
wornout
post Feb 13 2018, 09:32 PM
Post#1



Posts: 1,023
Joined: 17-November 13
From: Orewa New Zealand


I want some ideas on how to keep the stock level.
Main worksheet Called "Service Invoice" table called "Invoice" and the headers start at (B15)
the main 2 columns I will be dealing with are "Product"(B15) and "Quantity"(D15) the "Product is a data validation list cell the "Quantity" is just a normal cell
The stockSheet called "Stock" Table called "Table2" and the headers are in row 1 the columns I will be dealing with "Item"(A) "Reorder Level"(E) "In Stock"(F) "Sold"(G) "Total Sold"(H) these are not set in stone I can change them but not the Item as it is used in a few lookup equations.The "reorder level" is a predetermined amount
I am not adverse to doing macros or VBA but not allowed to have userforms


On the "Invoice" "Product" we use the drop-down list to find the Item we are selling and in the "Quantity we type in the quantity.
So what I would like to do
1) keep a telly of how much stock is left of each Item and if it drops to the reorder level have a msgbox pop up.
2) a column with how much is needed to get 5 past the reorder level
3) how many of each Item has been sold. Maybe this could go in a separate table so we could tell by date which would be the date of the invoice
I am open to different Ideas just no user forms(Which is what I am totally used to but fish out of water on the worksheet level with equations etc )
Go to the top of the page
 
wornout
post Feb 14 2018, 02:43 AM
Post#2



Posts: 1,023
Joined: 17-November 13
From: Orewa New Zealand


Ok does this make it eaiser to sugest a soultion

Service Invoice Tab table Invoice
....... B.................................C......................................D
Product............................Job................................Quantity
Caps..........................................................................5


Stock Tab Table2
....A................E......................F................G...............H
Item.......... always in stock...In Stock.........Sold.............Total sold
Caps..................10..................15

so in G it should be 5 and F should be 10 and sold should 5 so H increments G doesn't and F decreases but somewhere I have to be able to enter new stock

Go to the top of the page
 
dflak
post Feb 15 2018, 01:11 PM
Post#3


Utter Access VIP
Posts: 6,089
Joined: 22-June 04
From: North Carolina


The best way to manage inventory is to use a transaction based system. You stock it with an initial upload. Processes like manufacturing expend a certain amount. You do cycle counts and come up with a positive or negative difference and you resupply.

The amount on hand is never stored. It is always calculated as the sum of the transactions. Attached is a program that does this. The data are organized in such a way to be very amenable to pivot table analysis - you could put filters on the PT to show data where quantity less than x.

Take a look and see if it meets your needs. Instructions are included.
Attached File(s)
Attached File  Simple_Inventory.zip ( 155.22K )Number of downloads: 0
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st February 2018 - 04:19 AM