+ Reply to Thread
Results 1 to 6 of 6

Running Total for Stock On Hand

  1. #1
    Registered User
    Join Date
    05-29-2015
    Location
    richland, missouri
    MS-Off Ver
    2013
    Posts
    7

    Running Total for Stock On Hand

    I am trying to set up an inventory control sheet for use at a mission hospital. It is pretty basic in that each time med supply issues an item they are to track it on this spreadsheet (which keeps a running total of the stock on hand.) Each row is a different item. My problem is that to update the stock on hand column (eg D2) I need to subtract the amount dispersed (C2) from it. This causes a circular reference because I have = D2-C2 in cell D2 as I want to always update D2 to the present amount of Stock on Hand. I know how to reset C2 to zero with a macro when they open it back up but I don't know how to cleanly allow the Stock on Hand to be updated. I hope I explained this clearly. Thank you to any one who can help.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Running Total for Stock On Hand

    Hi,

    Would you upload the workbook so that we can see the request in context.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-29-2015
    Location
    richland, missouri
    MS-Off Ver
    2013
    Posts
    7

    Re: Running Total for Stock On Hand

    Here is a brief version of my spreadsheet. There is only one line item in this one
    but there will be up to 100 items (eg rows). There are other formulas and macros
    that I have left in but what I am really wanting is a way to add column J to column K
    each time a dispersement is entered. I have commented out one of my macros that I was
    using for that purpose because it was awkward and would not allow me to protect cells that
    I do not want the users to edit. Thanks so much.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-29-2015
    Location
    richland, missouri
    MS-Off Ver
    2013
    Posts
    7

    Re: Running Total for Stock On Hand

    I meant subtract column J from column K

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Running Total for Stock On Hand

    Are you hoping to have 1 row per item? So that there will only be 1 row containing Adhesive tape? If so, I think you are going to have to rethink this project as I don't see how this will work otherise. If you were to have a sheet listing all transactions and then a summary sheet, one row per item - that would be OK.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Registered User
    Join Date
    05-29-2015
    Location
    richland, missouri
    MS-Off Ver
    2013
    Posts
    7

    Re: Running Total for Stock On Hand

    Each row is a different item. They do not care about recording each transaction just a running total of the stock on hand for each item as well as a running total of dispersements for each item. They have cards they are required to write on for each dispersement transaction. As I am thinking this through I might need to keep all of those columns but I imagine there is a better way to reset values in cells with a macro than how I am doing it. My macro copies values when the workbook is open from column K and using paste special (values only) to the cells in column E.

+ 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. Stock/Inventory Control - Running Total
    By slntmnd in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-08-2018, 08:30 AM
  2. [SOLVED] Running total of Quantity on Hand Vs Sales Vs Quantity to Produce
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2015, 07:34 AM
  3. 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
  4. Pivot Chart Running Total - Removing Current Year Future Total
    By car3489 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-27-2013, 03:35 AM

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