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


Joined
May 7, 2018
Messages
4
Reaction score
0
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!

Inventory List.png
Inventory Pick List.png
 
Ad

Advertisements

Joined
Mar 14, 2018
Messages
555
Reaction score
175
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?
 
Joined
May 7, 2018
Messages
4
Reaction score
0
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?
 
Joined
Mar 14, 2018
Messages
555
Reaction score
175
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

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
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!
 
Joined
May 7, 2018
Messages
4
Reaction score
0
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.
 
Joined
May 7, 2018
Messages
4
Reaction score
0
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...
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,508
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top