I was entering monthly statement data (i.e., January, February, March, etc.) on a different row for the same checking account.
OK, you definitely only want one row per account that gets updated each month.
Does the reconciliation sheet mark the cooresponding row in the transaction sheet as being āreconciledā?
No, there are a couple other templates available that do that, but they are more work. I tried to keep this simple, so if you are keeping the accounts reconciled, then because the account totals are based upon the transactions, the transactions are reconciled.
I just updated the template from 1.3 to 1.6 and for some reason when I have it ascending the last (furthest out) transaction is sorted to the top rather than the bottom. When I look at the helper columns, in Column V the last transaction is always blank for sort order. If I manually add the next number it will re-sort correctly but as soon as new transactions are downloaded, Column V completely breaks if I do that.
Any ideas to fix it?
Do you have the āAccount Reconciliationā sheet installed and working? This template pulls account information from that one, namely the opening balance to use for the register that all transactions are then added/subtracted from. You should see that info being pulled in in N11 and O11, which is them pulled into row 4 of column V.
It is installed and filled out.
The first line of the reconcilation is orange and nothing is being pulled into N/O11, Iām guessing because the IFERROR is being triggered. Now that I take another look, it looks like I/J of the Account Reconciliation tab was the issue when it upgraded, those figures didnāt come along. Once I put those in, it seems to have fixed it.
1 Like
The Account Register add-in is very helpful. Thank you!
Iām having one problem: it seems to work fine for all of my accounts except one. When this particular account is selected, no data will populate. Any suggestions?
Strange. Click the ā+ā above column H to expand the hidden helper data. Do you see anything in the āTransactions for Chosen Account Since Openingā table? Anything showing up in O11?
Thanks for the reply. Yes, there is a value in O11 - it is the same value that I entered in the āTiller Opening Balanceā column for that account on the Reconciliation sheet. If I delete this value from the Recon sheet, it disappears from the O11 on the Register sheet.
Itās strange that any other account seems to work ok.
Ok, hereās something - I was wrong. Not every other account works ok. I have a couple account names with parentheses, and none of those accounts populate transactions into the register. So I think thereās a strong likelihood that the ( ) are causing the problem.
I think the account names are passed from the Tiller website to the Foundation Template. I change the name on the website (using the Edit button under Linked Accounts) and re-launched the Foundation Template, but that doesnāt seem to change the account names in the spreadsheet. I wonder if I need to start over with a new Foundation after I eliminate the ( ) from the account names? Any ideas?
I used Find & Replace to change the account name in the sheet to one without the ( ). Worked like a charm and now those accounts populate in the register.
Glad you got it worked out. Interesting to know parenthesis donāt work in account names
Agreed. Thanks again for the great tool.
I am having some trouble reconciling an account, but thatās probably a discussion for another thread.
I am also getting the āunable to add sheetā error. it looks like it adds the reconciliation sheet, but doesnāt rename it and then errors out before adding the register. Iāve tried adding the reconciliation sheet first and then I get "your spreadsheet already contains a sheet with a name, Account Reconciliation, that will conflict with this solution. "
I think this happened to me. I had accidently typed something into one of the cells of an account register and that poisoned it for all the registers. They came back when I simply deleted the errant entry.
Hey @jpfieber thanks for creating Account Register. I use it daily in my workflow to help me project future cash balances. Iāve used it for a few years but recently it started doing something odd that was not happening several months ago (at least I never noticed it before then).
I use it with 3 accounts, named Checking, Joint Checking, and Online Savings. Online Savings generally only gets one transaction a month, so I rarely look at it. I usually switch between Checking and Joint Checking.
The problem is that sometimes when I switch from one account to another, the register shows no transactions. Switching away from that account and back will then correctly populate the transactions. When Iāve noticed the problem in the last few months it seemed random.
This morning it is reproduceable: if I am looking at Online Savings, switching to either of the other two accounts will result in an empty register. Switching between the checking accounts always works but as soon as I view the savings account, the next account I look at will be blank.
The only thing I think might be related is that a few months ago the name of the savings account changed. It may have been when I started using the new Tiller Console. I think it had been simply āSavingsā before and it changed to āOnline Savingsā. That account name change completely broke Account Register (and other things in Tiller) until I tracked it down and fixed it in several sheets including the hidden part of Account Register. I donāt recall whether I had been using a custom name or not, but I do recall that bank changed their account names sometime last year.
Any ideas whatās wrong? This is extremely minor and easy to work around, just a small annoyance that I had not seen until recently.
I have had the no transactions show up occasionally, but never thought to try to track it down. If it occurs again, I have something to check to see if I get the same results. I do have a few accounts whose names have been updated.
I was able to reproduce the issue youāre describing. One of my accounts would show no transactions, but the others would, but sometimes I could get it to. Seems itās likely an issue with how QUERY handled different data types. If, for instance, most of your descriptions are text, but some of them are numbers, this can confuse QUERY. I havenāt run across this before, but itās definitely happening now.
I think I have a fix. Try replacing the formula in B4 with the following that doesnāt rely on QUERY:
=LET(
filtered, FILTER(R5:V, (R5:R >= P4) * (R5:R <= P5) * (R5:R <> "")),
headers, {"Date", "Description", "Amount", "Balance"},
IF(
ISERROR(filtered),
headers,
VSTACK(
headers,
CHOOSECOLS(
IF(
G6 = FALSE,
filtered,
SORT(filtered, 5, G8 = "Ascending")
),
1, 2, 3, 4
)
)
)
)
Let me know if it helps!
3 Likes
Thanks, this appears to fix it! My sheet had the formula in B3 instead of B4, so I inserted a blank row at the top for the formula to work. Maybe my template is an older version? My version is 1.60.
2 Likes