+ Reply to Thread
Results 1 to 5 of 5

lookup and calculate stock taken from inventory

  1. #1
    Registered User
    Join Date
    01-10-2019
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013 home
    Posts
    79

    lookup and calculate stock taken from inventory

    hello, I am looking for help with regards to setting up an inventory list that calculates how much stock has been used from another worksheet or workbook [stock], then calculating how much stock is needed to reorder in worksheet [inventory]. I would appreciate and HELP, as I have looked through the forums to find this simple task and cannot find any relating answers. the inventory should show the total of re-order in the "yellow column" from the stock used in the next worksheet.
    This is an unfinished workbook as my ultimate plan is to add more stock to the inventory and more names to the stock. I have attached the 2 worksheets for any answers to my query.
    Attached Files Attached Files

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: lookup and calculate stock taken from inventory

    Let me clear, Quantity in stock value is a opening stock or after consum / utilized stock.
    As i assume, in inventory to add one column which is utilized stock value.
    Suppose in "G3"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Quantity Reorder : "H3" :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See attach file.
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    01-10-2019
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013 home
    Posts
    79

    Re: lookup and calculate stock taken from inventory

    thank you for the adjustment 'avk', this is almost what i needed, however the quantity in reorder should be taken into account the reorder level to the total '12'. so with '6' in original stock and '2' been taken from stock on the 'stock' worksheet, i should have a quantity of 8 to reorder to make up the number of 12. that is why i'm having difficulty in understanding the workings of this calculation. i want to be able to automatically read what quantity to reorder on the date that i view this 'inventory' after the stock has been depleted by the names in the 'stock' worksheet. please

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: lookup and calculate stock taken from inventory

    In both worksheet date not mentioned. Reorder quantity 4 is ok corrected?

  5. #5
    Registered User
    Join Date
    01-10-2019
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013 home
    Posts
    79

    Re: lookup and calculate stock taken from inventory

    thank you avk. date is not an issue,
    i just want to be able to stocktake the stock as it is been used. using the "inventory" to calculate what i need and what is being taken. so i know how many i can order in the 'quantity in reorder'. so if i require a total of 12 in 'reorder level' but only have 6 currently in "Opening quantity in stock" , then 2 more are taken [quantity consumable] from "stock worksheet"; the 'Opening quantity in stock' should deplete down to 4, therefore i need to reorder in 'quantity in reorder' of 8 at the current time of opening the workbook. should i date it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 02-03-2019, 04:24 PM
  2. For stock trade profile, calculate the new positions and add new stock to the new row
    By sunnyray25 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-14-2018, 03:05 AM
  3. Trying to build Function to calculate average of stock returns, with stock prices as input
    By jameslaughlin129 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-16-2017, 05:18 PM
  4. I need a stock order sheet to be created from a stock inventory
    By tamhav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2014, 10:40 AM
  5. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  6. Replies: 0
    Last Post: 09-25-2012, 09:39 AM
  7. Inventory Stock Balancing with Inventory Data
    By bingbangboom in forum Excel General
    Replies: 2
    Last Post: 01-30-2011, 11:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1