I haven’t found the need for the full check register functions you have here, but I am constantly challenged with remembering online payments which I have scheduled but which haven’t hit my bank yet. If I’ve forgotten a big one, I might have cash flow issues, so as a reminder I have created a “Register scratch” manual account. When I schedule a bill for future payment, I enter it in Transactions with the expected date. Since the expected dates are future, they are always sorted to the top. The rows are conditionally formatted for high visibility. In the example below, I have 3 payments to the same vendor, but with with different categories. When the actual payment hits the bank, I can easily copy the proper category and note to the bank transaction with the matching amount and delete the scratch row.
Clever, thanks for sharing your solution!
I just updated the template to include the ability to sort Ascending or Descending. Once @randy flips the switch, it should show a new version (1.40) available in the Community Solutions template manager.
Change: Added ability to sort transactions ascending or descending.
Was the Excel version updated?
I was traveling. This is pushed and should be live at version 1.40 shortly.
I removed an unneeded helper column, and updated how sorting is done so it should work better when sorted Descending. The version will be 1.50 when @randy pushes it.
Version 1.50 is live now in the Tiller Community Solutions Add-on. Thanks for the incredible commitment to maintenance and enhancements, @jpfieber.
I have an account that has an apostrophe in its name (e.g. “Someone’s Checking”). This account is reconciled. However, in the “Account Register” (Google Sheets) only one “Opening Balance” transaction shows either in columns B-E or R-V.
it might not be helpful/necessary for others and too much work to explain to me, but I have a question. How would I add another column for ‘Category’ with the ability to choose a category from a dropdown as well? Thanks so much for all your work.
The issue is indeed with building queries when cell content has an apostrophe. To avoid this problem, all single quotes (apostrophes) have to be replaced with two double quotes. In particular, cell R5 must become
=IFERROR(IF(
VLOOKUP(G5,I16:J,2,FALSE)=“Asset”,
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, “select Col1,Col2,Col3 where Col1 >= date “””&TEXT(N11,“yyyy-mm-dd”)&“”" and Col4 MATCHES “”" & G5 & “”" Order by Col1, Col3 DESC Label Col1 “”“”, Col2 “”“”, Col3 “”“”“),
QUERY({INDIRECT(L4),INDIRECT(L5),INDIRECT(L6),INDIRECT(L7)}, “select Col1,Col2,Col3*-1 where Col1 >= date “””&TEXT(N11,“yyyy-mm-dd”)&”“” and Col4 MATCHES “”" & G5 & “”" Order by Col1, Col3 DESC Label Col1 “”“”, Col2 “”“”, Col3*-1 “”“”“)
),”")
Please consider updating the master template.
Thanks for pointing out this issue. I updated the template as you suggested, the version is now 1.60 and should be available as soon as @randy flips the switch.
Change: Updated to handle apostrophes in account names
I don’t think filtering by category would be a good fit for this tool since it’s goal is to display a running balance for an account. It’s certainly possible to alter the query to filter by a category drop down menu, but I think making the Balance column work and make sense would be difficult.
The “switch” has been flipped. Cheers!