+ Reply to Thread
Results 1 to 10 of 10

Stock/Inventory Control - Running Total

  1. #1
    Registered User
    Join Date
    04-22-2015
    Location
    Australia
    MS-Off Ver
    MO 2010
    Posts
    8

    Stock/Inventory Control - Running Total

    Hi there!

    I am trying to create an all-in-one excel spreadsheet for my inventory control. I have gotten as far as creating a master stock list including a code, description, cost, and quantity booked into the system originally.

    I am now working on a second sheet on which to keep a running total of all good incoming and outgoing using a vlookup to pull the information based on a code. The last thing I need to get down pat if a way to alter the running total "On Hand" column to not just take the value of stock originally booked and subtract the amount booked out each time. It is difficult for me to explain, but I am hoping someone can take a look at the attached file and offer some tips/a solution?

    Thanks in advance!

    Inventory Control Example.xls

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Stock/Inventory Control - Running Total

    i assume sheet 1 is inwards and sheet 2 is your outgoing?

    you want the running total in both sheets?

    are you going to have new sheets for every order inwards or the same sheet?
    if same how do you distinguish one order from the other and in turn get your price?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-22-2015
    Location
    Australia
    MS-Off Ver
    MO 2010
    Posts
    8

    Re: Stock/Inventory Control - Running Total

    Sorry, Sheet1 is my master stock list where Sheet2 grabs the item information from. Sheet2 is a running total for both incoming and outgoing stock. I didn't explain that very well.

    I would like the incoming/outgoing figures entered on Sheet2 to affect the On Hand value in Sheet1, however every time I enter a value, it calculates the original On Hand value minus the value entered on the new line, regardless of any previous entries.

  4. #4
    Registered User
    Join Date
    04-22-2015
    Location
    Australia
    MS-Off Ver
    MO 2010
    Posts
    8

    Re: Stock/Inventory Control - Running Total

    Every line on Sheet2 is one order, come billing time I can filter for specific jobs to grab all items used against the job number listed in column B.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Stock/Inventory Control - Running Total

    ok sheet 1 master...then quantity booked should be a formula instead of fixed value and it would be a total booked?

    then you manually type in incoming and outgoing on sheet 2 then?

  6. #6
    Registered User
    Join Date
    04-22-2015
    Location
    Australia
    MS-Off Ver
    MO 2010
    Posts
    8
    Quote Originally Posted by humdingaling View Post
    ok sheet 1 master...then quantity booked should be a formula instead of fixed value and it would be a total booked?

    then you manually type in incoming and outgoing on sheet 2 then?
    Quantity Booked is probably a misleading heading. It is the base amount I have purchased for stock and should also be the value I restock to. I figured it would be easier to start with a static number to work off, but if that isn't the case, I could perhaps enter all of my starting stock into the incoming column of Sheet2?

    And yes, incoming and outgoing values on Sheet2 are manually entered along with the job number and date.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Stock/Inventory Control - Running Total

    have a look at this to see if it does what you want
    the bits in orange are what you input in sheet 2

    the stock on hand formula needs to be extended when your list grows
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-22-2015
    Location
    Australia
    MS-Off Ver
    MO 2010
    Posts
    8

    Re: Stock/Inventory Control - Running Total

    That is perfect! Thank you so much.

    When you say it needs to be extended when the list grows, do you mean the master stock list or the running list on Sheet2?

    Also, sorry for the stupid question, extended how?

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Stock/Inventory Control - Running Total

    your list on sheet 2
    the formula on sheet1 only goes up to 28 at the moment
    =LOOKUP(9.99999999999999E+307,FIND($A2,Sheet2!$C$2:$C$28),Sheet2!$H$2:$H$28)

    increase C28 and H28 to however big you want
    ie
    you can put 3000 or something in there...just means it will look at 3000 lines even if there are many blanks

  10. #10
    Registered User
    Join Date
    06-08-2018
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    1

    Re: Stock/Inventory Control - Running Total

    Very helpful, thanks.

+ 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. Running inventory control/User form
    By Rekoons38 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2014, 12:12 PM
  2. Inventory and stock sheet running total calculation
    By vivek_vx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-16-2013, 06:56 AM
  3. running total formula for inventory
    By crusher949 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-02-2013, 02:02 AM
  4. Excel 2007 : Sumif - inventory/running total
    By CaliberChris in forum Excel General
    Replies: 2
    Last Post: 09-28-2011, 02:10 PM
  5. running total formula for inventory
    By trace1287 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-06-2009, 06:20 PM

Tags for this Thread

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