# Calculating running total for in lab stock

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

Manufacturing Association.xlsx

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

3. ## 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. ## 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. ## 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. ## Re: Calculating running total for in lab stock

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

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

8. ## 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. ## Re: Calculating running total for in lab stock

Originally Posted by ltelford51
Originally Posted by ltelford51
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. ## 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. ## 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.

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