Software

Ask something
Question Locked

Excel formulas

0 helpful
3 replies

by andyclayton31080

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by delbertpgh

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.

by absolutely

Copy & paste the picking list and the stock list into different work[i]sheets[/i] in the same work[i]book[/i]. If you're fully familiar with the distinction, I apologize for the [i]previous italics[/i], but many people are not, and the distinction [i]usually needs[/i] 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.

This discussion is closed to new replies.