Forum Discussion
How do I build a running total between worksheets within a workbook?
I spend several years as the Director of the HR/Payroll database of a major corporation (I'm retired now) and have experience with both the kinds of data you're talking about and Excel spreadsheet designs. It would help if you could provide a copy--(after removing any actual employee names and other identifiable data (e.g., SSNs); perhaps replacing them with the names of Disney characters)--of the actual workbook or the most recent two or three of these bi-weekly worksheets. You could post those real (or facsimile) files on OneDrive or GoogleDrive, and post a link here.
The reason that would help is that there are (as is often the case in Excel) probably at least three or four different ways to approach the issue you're describing, producing those running totals. Which is most appropriate would depend a lot on a better understanding of what your current spreadsheets look like.
Also: how many employees are we talking about? Are all employees paid bi-weekly or are some on a monthly or other frequency? And is it just the 401K data? Do you not need to do the same with such things as Soc Sec taxes, or other deductions?
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.