• Creator
  • #2269804

    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.

All Answers

  • Author
    • #2536498


      by andyclayton31080 ·

      In reply to Excel formulas


    • #2536462

      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.

    • #2536449

      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 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.

Viewing 2 reply threads