Help with creating dynamic inventory spreadsheet

[clstopher]

Hi,

I am new to this forum, and need some help. I have an inventory of thousands of different kinds of parts. I have one tab that has all of the parts and current stock levels. I have another tab to check parts in and out. To make it simple, I have used a list so that you can bring up the subset of tools and select what you need and check it out. What I want is for the first tab of quantities to update when tools are checked out...which it currently does. But my problem is that when the next person comes to check out a tool, by selecting the list it undoes that last check out and only has the latest transaction. Geez, I hope this makes sense. I've attached the spreadsheet here to reference. Any help would be greatly appreciated. I have a lot more parts to add to this, but wanted to make sure that I can actually do what I want before I get too far into in. Thanks so much in advance!

[Yraen]

I'll get something up for you tomorrow. I don't have time to finish it today.

[Tank997]

Not exactly sure what you needed from reading your question and looking at the sheet but see if the attached changes to your file give you what you need. I added a table to sheet2 so the formulas and data validation will be dynamic. You should consider removing the empty rows and columns sheets if you don't need them for future use.

Good luck and let me know if this works for you.

Tom

[clstopher]

Hi Tom,

Thank you for your response. Sorry that my question was a bit confusing. It is close to what I need. Part of the problem I have now is that now the quality remaining on sheet 1 and 2 don't match and also, when I select differnt parts from the drop down lists, some of them don't update, but the first part in the list does. I also noticed that it is adding differently. So, if I subtract 4 to check out, it subtracted more than that. I might not be understanding the formula. I really appreciate all your help. Having this inventory spreadsheet up and running will be such a huge help to me!

thank you!

[clstopher]

Thank you Yraen! I appreciate your help!

[Tank997]

If you looked at the Quantity Remaining on sheet2 for the last item for each part number it matches the quantity remaining on sheet 1. Sheet 2 gives you a running balance on hand after each transaction. So Part number AN960-2 shows quantity remaining of 50 on sheet 1 and 50 on row 17 of sheet 2. I guess that's how I read what you were looking for. I'm not seeing the issue with the drop down's not updating to the other cell values, can you give me an example? I also don't see quantity subtracting more than you enter in the Quantity checked out column of sheet 2. One thing that is causing an issue is I did not copy the formula all the way down in column C on sheet one. It ran into the blank row and stopped so just copy that down to the rest of the rows in the column.

[clstopher]

Hi Tom,

Sorry, I see what I was doing that makes perfect sense and the spreadsheet is almost perfectly what I need. The last part, and this is the part that I'm not sure is possible. Because there are so many people that will be checking things in and out everyday, I don't want to have an endless list of transactions on sheet 2. So, hypothetically if I had only one line on sheet 2, could I enter a transaction for part AN690-2 have that apply to the remaining quantity on sheet 1 and then on that same line select a different part enter the transaction and then have both parts, and continuing infinitely each part there after, have the quantity update and remain? Currently, if I check out a tool and then use the same line to check out a different tool, the original tool's quantity reverts back as if I hadn't entered the transaction. Please let me know if this doesn't make sense or of what I'm after is impossible. Thanks so much!

[Tank997]

I can think of a number of ways to do what you want to do but they all require VBA and I'm not a very good VBA guy so some of the others here in the VBA section should be able to help you with that. You could use a user form to post to the transactions to the table to update the inventory or you could just enter the data into one row and have a button to post it to the table and clear the range for the next transaction. Either way your table could get very large if you have a huge number of items and transactions. You can move and/or hide the table on another sheet if needed.

How often you you plan on updating your "Original Quantity On Hand" numbers and clearing out the transaction data? How important is it for you to keep and audit trail of the old transactions? If you have a huge number of items and a huge number of transactions you might want to consider doing this with a database program instead of Excel Access could do this or SQL server Express and it's free but will take more work to get it setup. I have also tested a free, for one user, opensource MRP program from http://www.dbamanufacturing.com/ it's very robust but it will also take time to learn and setup.

I would try the VBA route first.

[clstopher]

Tom,

As far as VBA goes, I am sure you are an expert compared to me lol. Thank you for all of your help! Ideally, I wouldn't be updating original quantities or clearing data very often. I'm trying to make it as low maintenence as possible. Partially because I am still in a many months long process of taking stock of everything and just starting this whole inventory process. Up until now there wasn't a system at all. If all else fails I will take a look at the other options that you suggested. Again, thank you thank you!

[Tank997]

Looking at what your trying to accomplish I would also ask if there was another system that would achieve an acceptable result? With high volume low cost items you should look into a KANBAN or bin system to control the inventory levels, they are a lot less work and can be just a accurate if not more so than trying to track every inventory movement. Ask yourself, is the juice worth the squeeze? I've worked with inventory and many inventory systems for over 30 years so trust me trying to over manage and control the small stuff can get very frustrating. On the other hand you are getting some valuable experience in Excel, which will always come in handy

[clstopher]

Thanks Tom. I will look into those other systems as well, those are ones that I hand't considered before. And yes, learning lots of new excel, which is a good thing!

Thanks!

[Yraen]

Here's a sheet that should do what you want. Here's the rules for the sheet.

In module1 there are two const at the top, ADDNEWROW and KEEPHISTORY. If ADDNEWROW is true then each time a part is checked in or out a new row will be created on sheet1 of the worksheet. If false, the same row, row 2, will be used for all transactions. if KEEPHISTORY is true, then the sheet TransactionHistory will be updated every time there is a change on the main sheet.

Each part type appears on the sheet Settings. These names can have spaces to keep them readable for everyone. This is also the name of the sheet, without any spaces, that the parts listing is located on.

Transactions are done real time. If you place a quantity in either the "Quantity Checked In" or "Quantity Checked Out" column, this number is added or subtracted from the on hand quantity, and history is recorded if it is set.

Right now all of the part sheets are hidden, you may make them visible if you wish. Feel free to rename Sheet1 to anything you want.

Room Inventory 1.xlsm

[Tank997]

Yraen, Nice work man, I love you VBA guys!! I do see one issue and I don't see what's causing it. On sheet1 I picked Washers in column A and AN960-2 in column B the on hand shows 65 I then entered 20 in column D but I meant to enter it in column E so I changed D to 0 and entered 20 in column E but the balance in column F went to 65 instead of 45. I changed the in and out numbers several times and the balance doesn't always update to the correct number. I also don't see anything posting on the transaction history sheet. Check it out and see if I'm missing something.

[Yraen]

Tank997,

Thanks for the compliment and thanks for catching that error. I had existing history from writing it, so when I cleared the history it had no row numbers greater than 1, so it never wrote history... oops.

Column D is an addition column. Changing this to 0 will add 0 to the total, to undo it you would either need to use column E or a negative number. I did go ahead and make it update the total on hand before the transaction though.

There's actually more functions in module1 than I use. I usually import all my GetRow and GetColumn functions then start to build from there. Those make life so much easier.

Here's the fixed file.

Room Inventory 1.xlsm

[clstopher]

Thank you so so much Yraen! This is perfect and you've made it so easy to use! This is amazing and you are the best! I can't thank you enough! And thanks to you too Tank997! I really appreciate everyone's help!

[Yraen]

You're welcome.