Forum Discussion
lookup date range and return certain data
First off, I am running excel 2007 so I may be limited in what I can do. I have a spreadsheet made for our employee SIMPLE IRA information. I enter the gross pay and the spreadsheet calculates the amount withheld. Recently, we changed banks and now I have to manually send in a check with a sheet that tells them each employees individual deposit and the company match. I made another spreadsheet to layout this information but I am not sure if there is a formula that will allow me to auto populate the cells based on the paydays from the other sheet. Basically, I would like the deposit sheet to read the date and take all the amounts for a month (e.g. September) and add each employee deposit together and the company match for each employee deposit. I have attached two screen shots to explain better.
3 Replies
First off, I am running excel 2007 so I may be limited in what I can do.
Is your organization large enough to pay for a subscription to Microsoft 365? You'd benefit (as an organization as well as individually) from updating things.
I made another spreadsheet to layout this information but I am not sure if there is a formula that will allow me to auto populate the cells based on the paydays from the other sheet. Basically, I would like the deposit sheet to read the date and take all the amounts for a month (e.g. September) and add each employee deposit together and the company match for each employee deposit. I have attached two screen shots
So I spent time in the 1980s and '90s, before retiring in 2002, as director of an HR/Payroll database system for a major corporation. With that background, I'd love to be able to help you here, to the extent I can.
Looking at the two screen images makes me wonder, first, if you have any flexibility in terms of how you initially record and store the historical data on employee participation in this SIMPLE IRA program. The image you display--of the spreadsheet you created "to layout the information"-- is almost a case of a separate table for each employee, rather than a single table for all employees; the latter, frankly, would be far more accessible as a source of information for that weekly deposit slip.
That question--the degree of flexibility in the organization of that source data--aside, is it possible for you to post, without any actual employee names or other identification, a copy of these two actual workbooks/spreadsheets, or at the very least a simple mockup. And IF there is perhaps another set of weekly data from the company that runs your payroll of this weekly deduction data, could that also be posted, again without any real Identifiers of real people.. If that's possible--an image is frankly hard to work with--if it's possible to post an actual workbook or two (or three), that would help us help you come up with the formulas or VBA routines to take data from the source to the deposit slip. Post them on OneDrive or GoogleDrive with a link pasted here that grants access.
- Thank you for your help. Sorry it has taken me so long to reply. I have to work on this in my spare time. Here is a link to a SAMPLE worksheet. We are a small company with only 5 employees and I am definitely open to making this more user friendly. We use QuickBooks Desktop to keep track of our payroll. Like I said before, I enter each employee's gross pay and have the spreadsheet set up for their withholdings. I then double-check the spreadsheet against the QuickBooks information to make sure they match. I was just hoping there would be a way for Excel to auto populate the deposit slip by referencing the date at the top of the screen for each month's deposit. Let me know what you think.
https://1drv.ms/x/s!AplFXDq_RywSiTM1ohcYPgnZ7oKr?e=sd5XSuI was just hoping there would be a way for Excel to auto populate the deposit slip by referencing the date at the top of the screen for each month's deposit. Let me know what you think.
And there is a way. HOWEVER, I have written the attached with the assumption that you can get your company to update your software at least to Excel 2021. Even better would be a Microsoft 365 subscription. These formulas won't work with your older version of Excel. If you're not able to update/upgrade, we'll have to see what we can do, but I strongly recommend the update.
What you now have, after you enter each pay cycles information on each employee who got paid that time, is a single Monthly_Deposit sheet. You enter the month's number in cell A4 (the year in E4 stays constant for the year, but can be changed for any subsequent year). And the Deposit sheet pulls the relevant data for each employee for that month.
There's basically one formula that does that, changing only based on the column headings for Gross Wages, and the Percentages.
=IFERROR(SUM(FILTER(Table1[Gross],(Table1[Employee]=Monthly_Deposit!B11)*(MONTH(Table1[Dates])=Monthly_Deposit!$A$4)*(YEAR(Table1[Dates]=Monthly_Deposit!$E$4)))),0)TO do this, I reorganized the raw data like this, into a table. Right now, because I just copied your data and transposed it to this vertical array, it has all of Employee A's data, followed by B's, and so forth. It could in the real world be totally by dates, one week at a time, with the employee's in "random" order, missing a row altogether (as Employee D did for a month).....that's not a problem when the data are arrayed as a table of transactions.
