Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Excel formulas

Locked

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.

Topic

Clarifications

In reply to Excel formulas

Clarifications

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

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.

While you’re waiting for Delbert & his VBA developer friend…

In reply to Excel formulas

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.

[Author]

Replies