personalised inventory system

Hi there

please Help: Trying to create my own personalized inventory system for my manufacturing center. Basically i have a table in a sheet with a list of stock items on hand, which i need linked to a delivery note system for items coming in and out. My problem is i have a drop-down (on the delivery note to the corresponding item on the stock list. I.e. If i randomly select a product from a drop-down list on my delivery note, it must update that specific item on the stock sheet, the problem is how do i link the items.

i have a sheet attached it will be easier to explain with this.

I added a new formula to columns D and E. I think that is what you are wanting to do. If not please explain. The formula I used was a Index & Match formula.
See the new attached file.

Hi jamfz

Wow thank you so much for your response, what you did what spot on in terms of matching the data to the drop down, thing is this being only a small part of the entire sheet I do have some queries.. as you will notice on the spread sheet there are many sheets with different headings these are all stock inventory. is it possible to link all these items in to the data validation source. so if I select a specific item it will update on the relevant sheet. Also another thing is it possible to separate the STOCK IN and STOCK OUT to a sheet of their own as this will be utilised by staff on a regular basis and printed. can the above be done, having things on separate sheets or do we need one long list of stock data on one sheet to create the data validation list

You can use the all stock page and have one long list or several separate lists on that sheet so your drop downs are specific for each sheet. Also yes it is possible to have stock in and out on their own sheet too. Let me know what you need.

Oops actually you can separate it out on the stock in and out sheets and use those lists for the drop down menus on each other sheet. You don't need the All Stock sheet for that. I answered a little to quickly before I thought out the plan. see this example attached

Hi Jamfz

Once again thank you so much, I have done my necessary editing and look, basically focus on the first 3 sheets, this is the
All STOCK SHEET - total stock on hand this is the sheet were the stock count will increase and decrease.

MANUFACTURE NOTE - this is the stock in stock out sheets, as you will notice the PRODUCED is stock in (increase value) and the STOCK USED is stock out (decrease
value) will you please assist with the formula, you will notice on the first row I made an attempt.

DELIVERY NOTES - this is a new addition and basically is also a stock out sheet for when we deliver to each store, it to must alter the figures in the ALL STOCK SHEET, are you able to include a formula for this as well.

as an after thought, if this is possible we would like to incorporate a RE-ORDER value within the ALL STOCK sheet for each item eg: steaks re-order amount 3, and patties will be 15... in other words the re-order value differ for each items and we will obviously input the initial amount somewhere... can assist with the setup if its possible...

and another thing is it possible to have a function that when we print the all stock sheet and the end of day it will only print the items required for re-ordering, a sort of print special function.

Its just to simply the process of us knowing what to order the next morning from the end of day print.

Thank you so much, your assistance is greatly appreciated... look forward to hearing from you.

Ok try this and see if it works. I tried to get everything you wanted and make it as simple and easy as possible. I gave each section on the All Stock sheet a named range to help with formulas. I did the same with the Delivery Note sheet. I created a new sheet for Reorder it has a auto run macro that hides blank cells so it only shows you what to reorder. As far as the formula for that I made a reorder column on the All Stock sheet. I assume this would be the quantity to order. So if your STOCK is less than your QUANTITY it will show the RE-ORDER value on the REORDER Sheet. I was not for sure what the quantity column was for. If this is incorrect let me know and I can adjust if needed. There is also some hidden columns on the All Stock sheet that keep track of what is delivered to give the total delivered in the Delivered Column. As far as printing the Re-Orders you can just print the REORDER sheet. Let me know if you need anything else. -Jason F.-

Hi Jason

Thank you I am busy going through the file now, one quick question, is it possible to have the auto complete function on each list drop down, I have seen some posts saying that it isn't possible unless, u bring the entire list of items above the cell range and then the auto complete will work, at the moment we would have to scroll though each item, to find what we looking for, a auto-complete will simplify that...

well if you want to you can put the entire list above where you want it and then hide the rows. but other than that there is no option for it.

I see so, some posts say you can use active x or VBA or a combo box method, seems quite in depth..

Anyhow I have a few questions for the current file, I realised that if we use the delivery note on a daily basis we will be deleting what was captured the previous day and thus will affect the stock sheet totals again. I.e. If I delivered 20 steaks, (4 to each store) this will successfully display 20 steak delivered. But now if I remove that the next day to start a new deliver sheet, the stock page will get affected, this kinda works the same for the manufacturing sheet.

Is there a way around this a sort of reset function for the sheets that get utilised (manufacture note and delivery note) and changes made all the time, but the ALL STOCK page stays updated, I hope this makes sense.

Next. The quantity column was originally there as the initial stock count... once we perform the initial stock count we will need to capture this somehow.. so the only way I figured was manually capturing it into a column, I noticed you have used the QUANTITY column in conjunction with the re-order function. The re-order value will not be changed unless the amount needs to be increased at some point. but basically how I would like it to work is if the stock reaches that amount, then items must appear on the re-order sheet.. so basically it only has to look at the stock amount.

Any ideas on the above

the re-order sheet is basically to show us when stock reaches that low amount or below the amount and needs to be re-ordered ( the figure captured in the re-order column on the ALL STOCK is the lowest level we can go to, so we know to re-order, the ordering amount we will decide.

can we make the re-order page just show the re-order item along with its current stock amount.

I have an idea to try. It will take me a little while to get to it. I am busy at work and doing this in my down time. The Stock /Reorder formula should be no problem. I was just thinking about it a little different. So if your stock goes below the number in the reorder column it goes to the reorder sheet. My question is does the quantity from the reorder column go with it or how do you know how many to order?
Also when you reset the sheets I am thinking that it should update the all stock sheet. The number in the stock column should move to the quantity column and clear the other cells on the all stock sheet to show your new inventory quantity. Then the sheet would work again for the next day retaining your current stock level. is this correct? Oh and the reset button would clear the manufacture and delivery note sheets.

I did just find an issue you have a quantity column on the all stock sheet and the meat products sheet and they are not tied together, therefore affecting the stock shown on each sheet. The stock numbers are different for the same item i.e. Mince 1kg

Hi Jason,

Thank you so much for all this help.,

correct if stock goes below or equal to reorder column it displays on reorder sheet. Can you also bring over the actual stock amount to the re-order sheet.. just so we can see it there to. No need for quantity re-order column to go with as this is just a static figure, all this figure is used for is when stock is equal or below it, but must be editable as in the future re-order amount may increase.. Just to put your mind at east the re-order column/amount is just the lowest point the stock can be at. not what we must order, we know what to order.....
________

I have an understanding of what you are saying for the reset function and that sounds fine.. so basically the QUANTITY column will become the IMPORTANT column displaying the actual stock on hand, once manufacturing is captured and deliveries are done

_____

forget the meat products sheet its null and void now, all the other sheets from meet product onwards are no longer needed, that is all combined into the ALL STOCK page now... initially I tried to categorise and have each section separate. but having 1 big all stock page is better.

regards
Kelly

I'm on the case. I will try to get you a working sheet today.

Updated-system 020915.xlsmthank you Jason, use this one so we have the latest copies each, I am attempting to put a welcome page on the front but will wait for your returned copy, please note on the manufacture note I have hidden columns for that auto complete function

Here you go. I think I have it the way you want it. I made you a reset button and it takes the stock at the end of the day and moves it to the Quantity column and then it clears all the other sheets.
Please note if you make any of the tables larger you will have to go adjust the macro to select the correct areas. You should be ready to get rid of all the other sheets too. I also protected the All Stock sheet to keep from accidentally typing in cells with the formulas in them. Let me know if you need anything else. Jason F.

Hi Jason
I trust you well, thank you for the latest updates, we are very happy and the formulas are working well, just a few things that I would like to alter and add.
Can you please bring through the actual stock column value into the re-order sheet, so we can see what we have on hand and can decide what to order without having to go to the all-stock sheet
Also we have added a UNIT COST column on the on the stock sheet, this is the cost per item and would like it to work in conjunction with the MANUFACTURE NOTE and DELIVERY NOTE PAGES. In order for us to monitor costs of items delivered to each store. i.e 1 pattie costs R7 (all stock sheet), if we select patties and a quantity of 36, it will automatically calculate the cost.
As you will notice I have made size changes on the delivery notes page, please confirm this hasn’t affected your macro settings.
Also is the a problem if we need to add more items to the all stock page, will it work for the other pages, if I simply inset a row and add the new item.
Look forward to hear from you.

Here you go. By the way nice welcome page.

Kelly try this file instead. I looked over it this weekend and made a few changes. I rewrote the reset macro using named ranges. Now you will be able to insert new lines if needed without changing the macro. The only thing you will have to do is copy the formulas to the new line. So as long as you insert new rows into the table and not just add to the bottom you won't have to adjust the macro. As always if you need anything else just let me known.

Hi Jason

Thank you so much again, can you plz check the delivery note function it isn't working on the latest received file, can you identify the problem, I am going to be updating the stock that we left out and doing to the finer touches on system.

Regards

Kelly

Oops Yes I can fix that real quick. I know what I did. do you have a latest version you want me to use or just fix the last one I uploaded?

Here you go I went ahead and fixed this one. It works now

HI

Sorry I didn't get you our latest file quick enough, can you do the fix on this one please.. you will notice what we have done as well, which is why I'm still sending. Just want to say thank you again for all the assistance. this one has the current live stock amounts, also added the date to some pages to auto-update each day, along with changing some sizes on delivery note, and some conditional formats were for appearance.

just another quick one, I opened your latest file and got this error:

Run-time error '1004': Active method of worksheet class failed.

and give a greyed out continue and end and debug and help buttons.

could this be an issue?

I will check it out and get back to you as soon as i can.

I ran the macro with the button several times and could not reproduce the error. I fixed the sheet and everything seems to be working. If you find anything else let me know.

Hi Jason

Wow this is so awesome, thank you so much we getting the hang of adding items and the functions are working so great. Just one thing noticed is that the re-order sheet isn't updating/displaying a few items I have recently added, I noticed you have some hidden columns on the re-order which works in conjunction with your code for the re-order sheet, I didn't want to attempt anything, can you please just rectify the reorder sheet for me please, items added include eggs, capers, captains spice etc etc.. amongst others. Perhaps you can explain why the re-order failed as to prevent this in the future, The Latest "LIVE" file is attached... oh and I haven't received that above error again but it wasn't related to the buttons it was when we opened the file for the first time. furthermore when we close the file it says picture too large being truncated.

regards

kelly

I found this for your Picture Problem. http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2007-01/msg02141.html This site says the following. "The reason for the error message, "The picture is too large and will be
truncated", is due to Microsoft's Clip Organizer. It tries to convert
anything that you copy into the clipboard into a picture format whether
you need it or not. I don't know how to avoid this, but if you're not
a fan of the Clip Organizer, you can remove it and then you won't get
the error messages anymore." There is more info on the site.

Hi Jason

Thank you so much, will download now and go through. I will also take a look at the info you found regarding the picture. Thank you so much for all your assistance, it is greatly appreciated.

Just a question: How much work would it be to insert a pop-up as you click the done buttons, pop up appear asking if the user has printed. if he clicks "yes" it continues to clear/run if he clicks "no" it goes back to the sheet, then he can print click done button and finish.

Hi Jason

Thank you so much, will download now and go through. I will also take a look at the info you found regarding the picture. Thank you so much for all your assistance, it is greatly appreciated.

Just a question: How much work would it be to insert a pop-up as you click the done buttons, pop up appears asking if the user has printed. if he clicks "yes" it continues to clear/run if he clicks "no" it goes back to the sheet, then he can print click done button and finish.

Hi Jason

Thank you so much, will download now and go through. I will also take a look at the info you found regarding the picture. Thank you so much for all your assistance, it is greatly appreciated.

Just a question: How much work would it be to insert a pop-up as you click the done buttons, pop up appears asking if the user has printed. if he clicks "yes" it continues to clear/run if he clicks "no" it goes back to the sheet, then he can print click done button and finish.

Hi Jason

Thank you so much, will download now and go through. I will also take a look at the info you found regarding the picture. Thank you so much for all your assistance, it is greatly appreciated.

Just a question: How much work would it be to insert a pop-up as you click the done buttons, pop up appears asking if the user has printed. if he clicks "yes" it continues to clear/run if he clicks "no" it goes back to the sheet, then he can print click done button and finish.

sorry I don't know why the reply appeared 3 times, my mistake

That would be fairly easy. Just need to create the popup and the text that goes in it. Then write a small macro to tell it what to do. Let me know if you want to proceed and send a new file if you have made changes. I have a sheet here that does almost the same thing.

Hi Jason

Did you get my msg?

Yes and sent you a reply.

Hi Jason

Within our stock sheet we have several items that use quite a few ingredients to make that 1 specific item. The issue is that the portion amounts used for these items are in grams and pretty difficult to manage, is there a formula or code that we can develop, that when you "PRODUCE" this product it automatically looks at a predefined "recipe list" and auto-fills the stock used sheet with the required stock used.

for example, we are manufacturing a basting sauce and to do so we use the following items.

special braai .100g
bbq spice .250g
mayonnaise 300ml
tomato sauce 150ml

so therefore we produce 1 units of basting sauce which we would usually feed in manually.
now what we would like if its possible is for those values on those products above to come off on our used stock sheet automatically and thus update the all stock sheet.

I'm sending the file back with an example table of how we would produce the basting sauce.

let me know if you understand what I'm saying and if its possible.

There might be a way to do that but I think that's a little over my head. Sorry, I'm not sure I can help on that part.

Hi Jason

I hear you, it is a quite complex idea to link with all stock workbook, just for curiosity, can you look at the sheet attached, check the formula at row 19, can you simplify that formula for me if posingredient sheet test.xlsxsible.

Here you go.

Hi Jason

Attached is our latest "LIVE" workbook, maybe take a look at the Ingredient Sheet, and see if its possible to link column A and Row 19 to the manufacture note.. basically column A is the product being made, the rest of the sheet is the fixed ingredient usage amount and the bottom is the total of each specific ingredient used.. in reference to my previous message regarding this, let me know if this can be done..

Regards

Kelly

I changed the cells that are highlighted in yellow. The cells in Column A need to be listed as they are on the manufacture sheet for the formulas I entered to work. If you want to do the same thing with the ingredients that should be achievable the same way for stock used as long as it is not used somewhere else too. That would cause conflicts between the sheets.

Also on a side note. The produced and used ranges on the manfacture sheet would need to be a lot bigger.

HI

Jason thanks for the latest stock control file, its quite late this side, but i'll take a look in the morning, in the mean time I have added our payroll system that I mentioned to you. Perhaps take a look and see if you can understand what we looking for.

Yes there is quite a time difference. It's just now 3:00 in the afternoon and almost time to get off work. I will take a look at your file when I get home.

Kelly, try this out and see what you think.

Hi Jason
The payroll is working like a dream shaves half hour off admin time thx so much for your help really.

And now we asking for more help again sorry lol.

I am attaching our latest stock file and a copy of one of our income expense workbooks for a shop which we are using at the moment (our values in the stock book are live sorry) and I would just like to know if you could do a column values link into the income expense workbook from the stock file.

We basically just want to link the values (just number totals eg: d186 - 1000 for abbotsford ) from the delivery note sheet in the stock workbook to the corresponding income expense workbook sheet and column. Its easier if you just have a look at the files then I think you will have an idea. We tried our best to link and it worked up to a point, but the problem came in when we tried to link more than one cell in the stock list ranges that the delivery notes look at. Another big problem is that the income expense sheet column has to be accumulative as it is used for bookkeeping so when we reset the delivery notes the totals in the inc exp sheet need to stay there.
Once again if we ask too much just say but your help in this regard would be greatly appreciated.
If it’s a case of copying and pasting into columns once one column has been linked then we could do the rest if its that easy but I doubt it.

We are learning so much by studying your code and then trying to emulate and adapt it which in turn is helping our skills and understanding of vba language at the same time. The code however sometimes stumps us at critical points and then its back to the drawing board which is asking you for help hehe.

Thx again Kelly and Ryan
captain haddocks stock control.xlsminc-exp-abb-oct 2015.xls

OK if I understand correctly you are wanting anything listed in the meat products section of the all stock sheet to have the values from the delivery sheet totals to show up in the expense sheet column for meat products. Same for spices, kitchen products and so on. I was confused by the values in column S in the expense sheet, as well as are the deliveries considered expenses? I think we would need another sheet to gather all the info since it is coming from 5 different columns. There we could add the totals for each section together and just have one total for each section. It would be something like IF ANYTHING IN ABBOTSFORD = ANYTHING IN MEATPRODUCTS RANGE THEN SHOW COST IN A CELL AND THEN ADD ALL THE TOTALS TOGETHER. After you have all the totals you could have a button with a macro that would copy those totals and paste the values to the other sheet. Please let me know what you think and give a little more detailed info so I can make sure I put everything where it goes.

Hi Jason
Yes I think your idea is perfect with the macro button used after all the info in del note done.
As far as the s column and other columns you see there that aren't in all stock those are things we buy every now and then for the shops from outside suppliers and input their values manually. You see we supply our shops with 80% of their stock from our stock list in our distribution centre but there are things coming in from other suppliers like fish and potatoes and that's what column S is and other columns like fridge foods or wilsonia. The workbooks are just standard income expense sheets that I use to get figures for my company bookkeeping, that's all basically.

The values on the delivery note are an expense as far as the shop (abbotsford) is concerned yes as they are basically our "customer" as well. This distribution centre is being set up for later on as we grow and try to start a chain with franchisees coming in and they would become our customer for certain products we manufacture ourselves. We are in a sense wholesalers to ourselves as well as retailers of the food at the moment. Complicated bookkeeping I can tell you lol:.
So I think your idea would work great and hopefully we could just duplicate the formulas for other shops or just copy the workbook.

thx again

Great I will try to work on it today. I'm getting ready to head to work now. I will get that other sheet set up and see if I can get things working.

Here is the sheet I messaged you about.captain haddocks stock control.xlsm

Hi Jason

We have rectified the errors you mentioned and also corrected the column headings on the ingredients sheet, they are now the same as on all stock, just used the = formula. Your formula on the manufacture note is awesome, the only thing is we basically need the same idea on the stock used section to pull the names and values from the ingredients sheet, to make it fully automatic, i.e the user will only have to enter what they produced and stock used will fill up automatically with the ingredients name and amount.

Thanks so much again Jason.. amended file attached

Hi Jason

Income and exp book attached with columns added as you mentioned.

Hi Jason ive attached the updated stock file a swell with the mixes added to all stock and del notes and man notes.
Is there any way of linking the ingredients with stock used even the other way round or something like feed the figures into ingredientsand let them show up on man note and stock used, just to avoid having to input those fractions manually as I fear that's where we will get mistakes that throw the whole stock value out.
Otherwise we will just have to monitor inputs from the staff member initially that's all
thx again

OK I think I got it. Try these out and see what you think. Also check you personal messages. Oh and let me know if I missed anything.