Forum Discussion
How do I build a running total between worksheets within a workbook?
Mathets, here is a copy of the modified worksheets. The Dec 5 sheet in the Total Column shows the formula I want to modify to pick up the previous worksheets total(s) and add to the current worksheets Totals to create the running balance. Thank you for your help! Pattie
Pattie -- looking at that set of worksheets raises in my mind a LOT of questions. There is what appears to be a LOT of redundancy in each spreadsheet (there are two rows for each person that are (or appear to be) identical: WHY?
- In particular, I wonder how open you are to re-thinking the whole process?
- Or whether that's even a possibility, given where this comes from and where it goes after this?
- Your very first posting in this thread says "I have a 401K worksheet created for each payroll period. Every other week the worksheet calculates each employees contribution for that payroll."
- How does that "creation" take place? Is that bi-weekly spreadsheet literally an output, exactly as we see it in your example, directly from the payroll program?
- Does the name of each tab--The "Nov 25 2022" get assigned automatically by some system, or do you do that "manually"
- Your very first posting in this thread says "I have a 401K worksheet created for each payroll period. Every other week the worksheet calculates each employees contribution for that payroll."
- In my first response to your posting here, I asked a few questions (to prime the pump, not to exhaust the possibilities) that you've not yet answered:
- how many employees are we talking about in total? Are ALL employees paid bi-weekly or are there some monthly? What about them? etc.
- how do you (or the system) track other deductions like SocSec taxes, benefits, all of which need to be tracked for the year as well? In other words, why are you only asking about the 401(k) side of things (or is that your responsibility, and other people take care of those other things?)?
If all you want to do is have a formula that applies to the former spreadsheet, that can probably be done relatively easily, but if I were in your place, I'd be wanting to make the whole process more efficient, to eliminate unnecessary redundancies, etc. That's why I'm asking those questions first. But even to answer that question, it would help if you could explain how each bi-weekly sheet gets created in the first place, who/what assigns the name to each bi-weekly. Give a more complete description of the big picture here, where these sheets come from and what happens with them after the most recent one is completed.
- Hi Mathetes, I appreciate your questions but they are irrelevant to the question I have. What you see before you is an example of a spreadsheet that I keep personally as a check against data entered to our 401K provider. The question I have is what is the formula that pulls a total from the previous worksheet, recognizing the fact that the previous worksheet changes each pay period. Thank you, Pattie
Here, Pattie, is a spreadsheet with a formula that works. It asks you to enter the name of the prior period's sheet in cell F1, and from there the formula picks up the values in that sheet and adds to them the current pay period's data. I recommend you name them without spaces, so, for example, NOV252022, rather than NOV 25 2022. The formula appears in column Z of the most recent sheet and the heart of it is the INDIRECT function. You can read about that function here.
I think you should be doing a lot more to make this a solid spreadsheet, but I get it -- you're not really interested in doing that, for whatever reason. This will enable you, I think, to keep adding a sheet in front of the most recent, entering a new name for that past sheet, and incorporating the values of the prior sheet with the current.
Quite seriously, there is a lot that could be done--I think should be done--to eliminate redundancies (which are never good in a spreadsheet design, since they just multiply the opportunities for errors to creep in), to simplify the process of tracking these 401(k) deductions. It's good that you're checking the results you get from the folks who do the rest of the work; that shows initiative, but I regret that you seem not to want to consider revisions in the fundamentals of your approach in doing that.
THAT doesn't make my questions irrelevant. You could still do me the courtesy -- especially since you are doing the creating of this workbook -- of answering how / why you name the tabs as you do and, more importantly, whether or not you have flexibility in that. If you insist on being rigid about that--as you are sounding--you could well be making your job more difficult, certainly less inviting to somebody who'd like to help. THAT is also a relevant observation.
And frankly, although you may think it irrelevant to understand the bigger picture, it would help somebody give you help if you provided that bigger picture. I'm sorry you don't appreciate that reality. Seriously.