Nov 22 2022 02:24 PM
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.
Nov 22 2022 06:48 PM
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?
Nov 25 2022 12:02 PM
Nov 26 2022 07:55 AM
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?
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.
Nov 27 2022 09:36 AM
Nov 27 2022 10:52 AM
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.
Nov 27 2022 07:40 PM - edited Nov 28 2022 07:07 AM
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.