Import Paystub Data to My Expenses and Income Spreadsheet

[rhexcel]

Hello,

Every two weeks I can download my paystub in Excel. Over the years I have been manually importing my pay and deductions into another Excel spreadsheet of my income and expenses. Now that I feel more comfortable with Excel, I'm turning to the Forum for the easiest way to automate this. I would prefer a non-macro solution but I'm open to trying a macro if it is simple enough. I'm guessing I will need to use 'Match' and 'Index' function for the date but I just need a little help to push me in the right direction.

1. Based on the 'Check Date' of the paystub, I would like certain cells to be auto-populated in the Income/Expenses OR summed first and then auto-populated in the Income/Expenses in the row corresponding to that same date.

2. This is just a made up example of a paystub and actual Income/Expenses spreadsheet. Not all columns are together or will necessarily be in this same order.


Thanks in advance.

[JeteMc]

Note that the following will only work if all of the paystubs will be kept in the same workbook as the All Income and Expenses 2020 sheet.
1. Insert a new column A and populate from A7 down using: =IF(MOD(ROWS(A$1:A14),14)=0,MONTH(B7)&"-"&DAY(B7)&"-"&RIGHT(YEAR(B7),2)&" Payroll","")
2. Populate column C (row 7 and down) using: =IFERROR(SUM(INDIRECT("'"&A7&"'!B23:B25")),"")
Note that columns D, E, I, J etc. could be similarly populated.
Let us know if you have any questions.

[rhexcel]

Thank you so much JeteMc! This is perfect and I can mark this as "SOLVED." I do have two questions just for my own learning. I went through the formulas to try to write out in words your logic:

1. For =IF(MOD(ROWS(A$1:A14),14)=0,MONTH(B7)&"-"&DAY(B7)&"-"&RIGHT(YEAR(B7),2)&" Payroll","") I have: "If the remainder of the number of rows from A1 to this row divided by 14 is 0, then return the date in B with the word "payroll", if the remainder is not 0, return a blank cell.

2. For =IFERROR(SUM(INDIRECT("'"&A7&"'!B23:B25")),"") I get: create reference to worksheet "This week's date Payroll"!Range Oasdi:Federal Withholding and sum of that range if no error, return nothing if error.

3. I was surprised to see that the formula in column A did not return the circular reference warning since the result is also in column A. What is the difference between "row" and "sum" that results in the difference? I sort of understand.

Thank you so much. I never would have thought of MOD and it comes up so much in solutions on this forum. Thanks again!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[JeteMc]

As to #1, you are correct.
As to #2, also correct, because without the IFERROR the formula would display #REF for cells C8 and down which either have no value in the corresponding cell in column A or which do not find the sheet name displayed in column A.
As to #3, the ROWS function doesn't produce a circular reference because it is using the array argument (A$1:A14) to produce a value (14), it is not attempting to retrieve values from those cells as the SUM function does. (I hope that makes sense).
As to the first sentence: You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.