Forum Discussion
How do I build a running total between worksheets within a workbook?
I have a 401K worksheet created for each payroll period. Every other week the worksheet calculates each employees contribution for that payroll. But, I want to keep a current running total on each worksheet of each employee's contribution amount YTD, without having to build the formula each time. The formula I want to write needs to look back to the previous worksheet, pick up the YTD total for each employee and add it to the current worksheets employee payroll total contribution and fill in the the new YTD total. My current formula is written: ='NOV 25 2022'!Z8+X8 I need to know how to tell EXCEL to go to the preceding worksheet to pick up the correct total. Do I add # of days to the Date? Do I change the named worksheets to numeric values? Each pay period I need it to look back to the most recent worksheet for that total. Thanks for your help.
6 Replies
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?
- https://docs.google.com/spreadsheets/d/1g2P6HtcmOt_3tqAL8HU-JwOOI9yZhJpx/edit?usp=share_link&ouid=110523832930152663884&rtpof=true&sd=true
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! PattiePattie -- 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.
