(Basic excel user) I would like to create a more automated inventory input process

[PuSHoffice]

Hello all, I posted this in another section but was told because I am looking for more of a macro or VBA style command I should post here.

Here is what I have,
A basic spreadsheet with an inventory list of parts and their respective quantities, it has some basic functions, but all those functions are manually entered. (example) when a part comes in, you must manually search for it, then go to the quantity column and add in the amount received, then you have to change the date of receive to that days date, then you select a from a drop down list that it has been inventoried (It changes the whole rows color based on the amount on hand vs amount required for current jobs). This leads to problems when you have 100s of parts with very similar names or when you are entering hundreds of parts in at one time (which happens a lot).

What I would like to have

At the top of the sheet I would have an Inventory In button, click on it and a search menu appears, using our barcode scan software to automatically enter the part number in the box it would search for that part in the list, once found it would change the box to ask how much inventory has been received, again you could manually enter or scan the code for amount into the box, it would then change the total inventory number to reflect the input, it would change the line from ordered to inventoried, it would erase all information in the amount order box for that part, and it would change the last date received box to that days date.


I know this is probably asking a lot but I would love to learn how to do this. Any help is greatly appreciated

[ppgab]

It is usually better if you could provide some pictures or the excel file, but I have an generic solution for you, if you know how to fiddle with it it should solve your problem

Basically just have an "ID" cell and an "Amount" one, put a button a "Add" button near them, with this macro :

Remember to change the sheet names and cell coordinates

Please Login or Register  to view this content.

[silentcarl]

Inventory Control.xlsm

This answers nothing for you, but I would love for you to take a look at it and see if you can implement any of it into your system. It is an inventory control I wrote (with help from this site) that has a reorder quantity and can email when inventory levels are low.

[PuSHoffice]

Ok I think I attached this correctly

[PuSHoffice]

ppgab, thanks for the help so far, I thought I had attached the excel file on my first post, but apparently I fail at the internet so I posted is down below.

[ppgab]

ppgab, thanks for the help so far, I thought I had attached the excel file on my first post, but apparently I fail at the internet so I posted is down below.

Sorry I have no idea what is going on your worksheet, I'd suggest looking into basic VBA programming or explain in detail what you want to achieve there.

For example, search what in which column of which worksheet? If found change what in that column?

[PuSHoffice]

Sorry I have no idea what is going on your worksheet, I'd suggest looking into basic VBA programming or explain in detail what you want to achieve there.

For example, search what in which column of which worksheet? If found change what in that column?

I will try to explain our horrible mess as best I can (I did not make this thing, but it has fallen on me to fix it), The only tab that needs to be worried about is the Master tab, In master you we have columns- Part (common in house name) MFG# (the part number from the MFG) MFG(self explanatory) Required (the total amount of parts required to run the production line) Have (how many are in stock currently) Need (positive numbers represent overstock, negative numbers represent shortages) Last Received (when the last order came in) QTY on order (how many were ordered) Ordered (drop down menu to select if a part is was inventoried in, is on order, or for some reason we are short but the part is not on order, IE not stock) all the other columns are just for in house work with pricing and such. I would like to have an inventory button, that will ask for a part number, once entered it will locate that row and ask for a quantity, entering a quantity will add into the Have column for that part, it will then update the Date received cell of that row to that days date, it will delete the QTY on order, and it will change the Ordered cell from ordered to Inventoried. Hopefully that makes any sense.

[ppgab]

I will try to explain our horrible mess as best I can (I did not make this thing, but it has fallen on me to fix it), The only tab that needs to be worried about is the Master tab, In master you we have columns- Part (common in house name) MFG# (the part number from the MFG) MFG(self explanatory) Required (the total amount of parts required to run the production line) Have (how many are in stock currently) Need (positive numbers represent overstock, negative numbers represent shortages) Last Received (when the last order came in) QTY on order (how many were ordered) Ordered (drop down menu to select if a part is was inventoried in, is on order, or for some reason we are short but the part is not on order, IE not stock) all the other columns are just for in house work with pricing and such. I would like to have an inventory button, that will ask for a part number, once entered it will locate that row and ask for a quantity, entering a quantity will add into the Have column for that part, it will then update the Date received cell of that row to that days date, it will delete the QTY on order, and it will change the Ordered cell from ordered to Inventoried. Hopefully that makes any sense.

Hey, I was wondering if you still need help with this? Totally forgot