I just duplicated this sheet 3x for my 3 sources of income.
3 Likes
I really like this for the extra level of detail it gives me on being able to track how much tax Iāve paid etc.
Iām struggling with one thing though which is my 401k match. This doesnāt show on my Paycheck but when the money goes into my 401k account it goes in as a lump sum
One simple solution is just to split up that transaction each pay period, but that seems repetitive and manual and the goal is to minimize that sort of thing, so Iām wondering if thereās any way I could account for 401k matching with this? Your thoughts?.
I do my 401 match separately as my company used Vanguard and Vanguard pulls in contributions, whether from me or from an outside source, like my company.
On the paycheck deduction categories, itās marked as an expense and on Vanguardās side, both are marked as a transfer. I donāt know if thatās the ārightā way, but itās been working for me for a while.
Column AI is not working for me. Although I followed all of the instructions to the letter in Section 1, I am seeing an error in AI5. It is giving me this error:
=ARRAYFORMULA(IFNA(TRANSPOSE(IF(#REF!<>"",VLOOKUP(AO4,$F4:$AF,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27},True),INDEX($P4:$AF4)))))
All other rows in that column are blank without any formula. Whatās the issue? Is there an error in the worksheet? I added this sheet after but was listed as a New item under Explore in the Community Solutions bar.
What is the #REF supposed to be?
Hereās what the formula is supposed to be:
=ARRAYFORMULA(IFNA(TRANSPOSE(IF($F4<>"",VLOOKUP(AO4,$F4:$AF,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27},True),INDEX($G4:$AF4)))))
The thing is, that formula should be in cell AI4. I also notice that your formula, besides having the #REF error, the end references ā$P4:$AF4ā instead of ā$G4:$AF4ā. That part of the formula is grabbing the list of paycheck info, which should start in column G. Because yours shows column P, it seems like that part of the sheet got altered. If just fixing the formula in AI4/AI5 doesnāt do it, youāll probably want to āRestoreā the sheet and start again.
I had to restore it a couple of times and I found another error. In the directions, it says āUsing your latest paycheck stub/statement, enter the date you got paid in F5ā This information should say āUsing your latest paycheck stub/statement, enter the date you got paid in F4ā It works when I enter it in that line instead of having it blank. I was reading the directions and taking it literally. However, with that change, it works! Thanks!
Glad you got it working! @heather I donāt see the ability to edit my original post to fix this, is that no longer possible?
Are you saying you canāt edit the first entry in this topic/thread?
Correct. I can change the title, but I donāt get the āEdit this postā pencil button at the bottom. Seems like that goes away after a period of time, since I can edit the last post I made in this thread, but I canāt edit any earlier ones.
Just found an obscure setting, @jpfieber, and changed it. Try again.
That worked, thanks!
This is great! Thanks jpfieber!
As suggested I made a copy of this to track my wifeās paycheck separately from mine.
One issue I had to address was that in my transactions sheet I have a āreconciliationā column between āAmountā and āNoteā. This was not handled properly in the generated transactions section. I fixed this by inserting an empty column AW in the generator sheet (Section 4).
I like the way you have created sections to keep things organized and hidden. I like this approach better than the convention being followed in the other tiller sheets of hiding the āworkingā columns on the right.
Glad itās working for you! Matching all the Transactions columns does get tricky. Iāve had issues when inserting a new column on my Transactions sheet after the Payroll sheet has been established. In any case, the easy solution to those issues is to select AT3, and use the fill handle to copy the formula to the right till you get to BZ3. This should ārefindā all your Transactions columns.
Thanks for making this, I have been considering how to do something similar. Yours is better than my ideas!
I also modified it to work for mortgage payments and escrows. My mortgage company imports as a single line item for everything, no separate escrow balance or interest line items. Now I can see this being adapted for loan payments as well.
4 Likes
Hey all,
Iām having an issue with the āPaycheck deduction transactionsā section of this template. Specifically the āATā column shows a random number (44804) from AT4 through AT23. Iāve deleted and then reinstalled the template, but Iām still having the issue. I feel like Iām chasing my tail around here, any ideas for a solution?
Thanks in advance,
Leif
The numbers youāre seeing are unformatted dates. Since I donāt know which columns youāll have dates in, I didnāt pre-format any column in that area, and ideally you shouldnāt need to. Once you paste that section into your Transactions sheet as āValuesā they should take on the formatting you have there, so dates will look right again. If youād still like the date related columns like AT to look like dates, just click the column header for AT, go to Format\Number\Date and it should look the way youāre expecting.
Thanks a ton for the response @jpfieber (and your hard work!)ā¦ problem ended up being that I didnāt copy the columns starting from from āASā (the tiller logo column) in the Paycheck Deduction Transactions worksheet and then paste it into the āAā column on the main transactions worksheet. Total user error I suppose.
I thought if I was only copying starting with the āATā columns and pasting them into the āDateā Column on the Main Transactions sheet it would work, but it didnāt for me initially.
1 Like
I have tried it about 5 times with new sheets but I also have some issue with the date override drop down. It has blanks or references odd dates. There seems to be some bugs where adding rows throws it off and I cannot get the row selected properly to copy/paste into transactions. Any thoughts?
Adding rows can definitely cause issues if you add to the beginning or end of a range. Better to add new paychecks to the end of the list, and then select and sort them.
Yes agree. I downloaded a fresh copy and only manually entered one more paycheck underneath and for me it still does not auto select row on override.