Excel Changing amounts from one sheet to another using specific item #

[Padraic]

Here's my problem. I'm trying to change the quantity (red box) that corresponds to the item number (blue box) after entering the requested amount (green box). How do I do this? It's an inventory management I'm trying to build. These are on two separate sheets. The 'Inventory List' (first picture) and the 'Inventory Pick List' (second picture) If you need the template, I downloaded it through Microsoft Excel. It's called "Warehouse Inventory". Thanks for your help!

 

[Abraham Andres Luna]

You need to write a function that will do that math for you. So you want to subtract the "Pick Qty" from the "Total Qty" to come up with the "Available Qty"? Am I understanding that correctly?

 

[Padraic]

You need to write a function that will do that math for you. So you want to subtract the "Pick Qty" from the "Total Qty" to come up with the "Available Qty"? Am I understanding that correctly?

Yes, something like that. I want to be able to put in a quantity on the pull page, and then have it change the available quantity automatically. As it is now, it's only displaying what's available no matter what. I'd have to go back and manually change the quantities, and when you're dealing with 10+ items at a time, that gets tedious. Does this make sense?

 

[Abraham Andres Luna]

This is going to take some custom functions, I'm a little bit rusty in VBA programming in Excel. But if you want to update the available quantity on the other sheet when you enter an amount on the pull page you could add a button that will do the work for you. After adding the button you would create a handler for when the button is clicked. In that event handler you would write the code to update the available amount on the first sheet.

Here is a link to get you started: https://msdn.microsoft.com/en-us/vba/office-shared-vba/articles/getting-started-with-vba-in-office

If you need more help just ask and I will try my best.

 

[Becky]

I think it would be possible to do this without VBA - have you tried using VLOOKUP? Essentially all you would need to do is to use it to look up the total stock, lookup the amount picked, and subtract one from the other.

ie =VLOOKUP(total)-VLOOKUP(pick)

Let me know if I've missed something though!

 

[Padraic]

I think it would be possible to do this without VBA - have you tried using VLOOKUP? Essentially all you would need to do is to use it to look up the total stock, lookup the amount picked, and subtract one from the other.

ie =VLOOKUP(total)-VLOOKUP(pick)

Let me know if I've missed something though!

That would probably work, but I have to make sure that it's looking up the value that's associated with the Item #. If I just do that, it'll only work for the current line. I'm trying to make it update the quantity on hand when I create a Pick order.

 

[Padraic]

This is going to take some custom functions, I'm a little bit rusty in VBA programming in Excel. But if you want to update the available quantity on the other sheet when you enter an amount on the pull page you could add a button that will do the work for you. After adding the button you would create a handler for when the button is clicked. In that event handler you would write the code to update the available amount on the first sheet.

Here is a link to get you started: https://msdn.microsoft.com/en-us/vba/office-shared-vba/articles/getting-started-with-vba-in-office

If you need more help just ask and I will try my best.

Unfortunately, I don't have time to learn a programing language. I'm trying to keep this as simple as possible so that way, if I'm out sick or on vacation, I can have someone handle this while I'm gone. It feels like I'm asking to reinvent the wheel here. And if I am, I'm sorry...

 

[Becky]

That would probably work, but I have to make sure that it's looking up the value that's associated with the Item #. If I just do that, it'll only work for the current line. I'm trying to make it update the quantity on hand when I create a Pick order.

Not sure I follow... From what I understand, you manually enter the total stock number (on the Inventory List) and the pick number, is that right? And you want to generate the amount available, which is the total stock less the pick quantity? Assuming you have the spreadsheet set to automatically calculate formulae, then if you used a formula to calculate the amount of each product available then when you alter either the total stock or pick figures the amount available would update automatically.