Excel formulas

By andyclayton31080 ·
If I have a stock list and a picking list in different wookbooks, and in the picking list I had to enter a code and quantity, and when I did that I wanted it to look for that code in my stock list, and remove the quantity that I entered in the picking list from my stock list.
for example:-
Code Quantity
taglarge 18
I would want it to look for that code in my stock list then remove the quantity for that item from my stock. so I can save time filling in two forms all the time.
THX hope you understand that and hope someone can help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I think you need a VBA program, not a formula in a cell

by DelbertPGH In reply to Excel formulas

Use a program to manage a transaction entry window, to both post new entries to the transaction record, and to decrement your stock list. It sounds like you want to maintain two worksheets, one a transaction register and the other a stock list, and have the simple act of entering a new row of transaction data perform an update on the inventory. I don't see how that is going to work.

Collapse -

While you're waiting for Delbert & his VBA developer friend...

by Absolutely In reply to Excel formulas

Copy & paste the picking list and the stock list into different worksheets in the same workbook. If you're fully familiar with the distinction, I apologize for the previous italics, but many people are not, and the distinction usually needs to be emphasized, in my experience.

Now, you can use a vlookup function in the stock list worksheet to copy the quantity from the picklist worksheet, if and only if the code matches. The function in the stock list worksheet would look like =vlookup(picklist!$a1:$b65536,2,false), assuming that the code is in column a & the quantity is in column b of a worksheet called 'picklist'.

The next step is to use the result of that function, and subtract from the current number in stock. After that, the next step is to start using MS Access & treat these picking lists as link tables, with the functions described above replaced by queries.

Related Discussions

Related Forums