+ Reply to Thread
Results 1 to 11 of 11

Calculating running total for in lab stock

  1. #1
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Calculating running total for in lab stock

    I have several part numbers that come in and out of my lab for troubleshooting. To appease the inventory control folks I am trying to keep a running total of what is sitting in my lab at the end of each day. There are units that are coming in and going out every day.

    Currently I have a TAB with the IN portion which is what is brought in on a day by day basis, then I have a sheet that is OUT which is obviously the parts leaving the area. Then I have the TOTAL IN LAB TAB that is calculating the totals of each part number. That formula looks like this. =(C2+IN!D2)-OUT!D2 which is taking the day before total adding the IN number from today and then subtracting the OUT for the current day. Everything goes well until I try to work out the Monday total, because the other formulas depend on the day before obviously I am given a circular reference to this.

    Please help. I am sure it is something simple but it is bugging me.

    Manufacturing Association.xlsx

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Calculating running total for in lab stock

    One solution might be to insert a column (which could then be hidden) after the part numbers on the Total in Lab tab. You can then copy your formula in E2, Tuesday's count of part number 511857 after the insert has been made, back to D2 and double click it down column D. I would attach a copy of the file with these changes applied, but unfortunately the original file has locked pages which is preventing me from removing personal information.
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Re: Calculating running total for in lab stock

    Do you mean after every day insert the column or just after the Friday column. I was working along these lines before but could not figure it out. I think you have something though.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Calculating running total for in lab stock

    The inserted column would go between the part number column and the Monday column on the Total in Lab tab.
    Here is a copy of your file with the column inserted and the formula copied over.
    Copy of Manufacturing Association.xlsx
    Let me know if you have any questions.

  5. #5
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Re: Calculating running total for in lab stock

    Sorry JeteMc I don't think I have done this right.

    I did this but I am not sure it did what I needed to be done.

    "You can then copy your formula in E2, Tuesday's count of part number 511857 after the insert has been made, back to D2 and double click it down column D"

    Will this also allow for everyday updates and the totals will run on the total tab?

    Sorry about the confusion, I got a little lost in this and trying to find my way out.

    Thanks

  6. #6
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Re: Calculating running total for in lab stock

    Why the hidden column if I did not put anything in there?

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Calculating running total for in lab stock

    Just aesthetics, would not change the performance of the spreadsheet whether you hide it or not. A lot of people do not want to have extra columns in their spreadsheet for whatever reason, although there are 16,000+ columns, so to me hiding one or a few is trivial.
    Last edited by JeteMc; 11-12-2015 at 05:56 PM.

  8. #8
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Re: Calculating running total for in lab stock

    What happens when the week rolls over? I did a little test and added numbers to every day, but if the lab is not empty on Friday how do I account for what is there when I come in Monday?

    Thanks again

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Calculating running total for in lab stock

    Quote Originally Posted by ltelford51 View Post
    Please help. I am sure it is something simple but it is bugging me.
    Quote Originally Posted by ltelford51 View Post
    but if the lab is not empty on Friday how do I account for what is there when I come in Monday?
    Not so sure that this is simple. A circular calculation would occur under the scenario that you have mentioned, as the Total in Lab numbers on Monday are part of the calculation for the TIL numbers on Friday which would then feed back into the TIL for Monday. Seems like a redesign of the spreadsheet would be more likely to solve this than a formula. For instance of each column was assigned a date that would provide you with inventory information for 44+ years. Even better if each row was assigned a date that would provide you with inventory information for 2872+years.
    In my mind a redesign would give you better record keeping so that if a question were to arise about the inventory of day in the previous week etc., or about a particular date, you have precise records.
    Sorry not to be of more help with a formula.

  10. #10
    Registered User
    Join Date
    11-12-2015
    Location
    Ripon, WI
    MS-Off Ver
    2010
    Posts
    6

    Re: Calculating running total for in lab stock

    My wife and I were talking about that very thing. If we use the actual date then we could use a formula to roll it over and keep a running total. Thanks for your help. I will work on the redesign I think and see what happens.

    Thanks so much

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Calculating running total for in lab stock

    You're welcome and thank you for the feedback. Hope that you find a redesign that works for you. Let us know if we can help.

+ 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. Running Total for Stock On Hand
    By roamanzambia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-02-2015, 06:04 AM
  3. Calculating a running total
    By RustyCopperPenny in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2014, 04:21 PM
  4. [SOLVED] Calculating commissions with varying percentages based on running total
    By morgan74 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2013, 01:04 AM
  5. 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
  6. calculating the total from a running totalizer that resets
    By superchill435 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-10-2012, 11:04 AM
  7. Running Total for Calculating Sick Time
    By Mart1n in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-06-2009, 09:17 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