This is an update to my Excel Transaction Tracker which now doesn’t require any other sheets to run.
With this tracker, you can select a category and instantly see transactions. Want to refine? Just select another drop down filter. Want to see all transactions for a month, for a specific account? You simply select the month in the drop down and then account. All filters are optional and you can use them in tandem with each other. (Except for the date filter which will have priority over the month filters.)
Installation
Verify Note column exists in Transaction sheet. Please go to your Transaction sheet and if you don’t have a Note column, please add it to the right of all other columns.
Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
At this point your new template should be functional and linked to your local workbook’s data.
Usage
Simply select the filter you wish to use and your data will be reflected below, along with the total amount.
Two things to keep in mind:
If you are putting in a specific date, that will negate the month filter.
If you use this sheet on the web, the “Has Note” checkbox won’t work. If you wish to query transactions with a note, just type “True” in the field.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Yes
Thank you!! this is amazing. It’s so fast and so good.
I can’t seem to make Type and Group work and would love to add Accounts but I can’t figure out how this amazing thing works. Where are the mechanics?
For now, Type just lets you filter the group filter to make it easier but doesn’t filter the results. I can add accounts, and not sure why I missed that. I’ll see if I can do it today.
But if you want to explore, the query is in B9 and the data comes from the hidden columns.
You’re very welcome. It should be straightforward to add account since it is on the Transactions sheet. Otherwise I’ll get to it later and update. I accidentally submitted two posts but they are stuck in pending mode but I’ll add it to show and tell for Google
I fixed the Type filter, I saw what you meant, but it still doesn’t filter the results, just the group/category filters.
I did add Account to the filter and results. Transaction Tracker - Google Sheets
Hi, sorry if I wasn’t clear. I meant for the downloading/updating of the template, not actual usage of the template or Tiller. I found it easier to do upgrades using the program rather the web version.
Thanks so much for this, @yossiea! Your Transaction Tracker is simple but original and super handy. It is the Swiss-army knife of slicing and dicing transactions history. The UX is simple and intuitive. The performance is responsive. Bonus points for knocking it out a Transaction Tracker sibling in Google Sheets. Hat’s off.
We are super excited to award our first 2022 Microsoft Excel Builders Challenge spot award, a $100 gift certificate.
If you’re open to a little developer-to-developer feedback for a future release, here is what I’ve got:
The Category filter doesn’t seem to be wired in A10.
The sheet doesn’t work great when the Tags and/or Note are not present. (They are not standard in our Excel Foundation Template.) Is there a way to make the sheet more tolerant of failures to find these columns?
When no results are found, the user sees a #CALC error. Consider wrapping A10 in an IFERROR() function with a “No results” output. (Though this might hide other issues— like columns not being present.)
Consider changing the user-input fields backgrounds to a light green color to indicate they are user-editable.
I like the Transaction Tracker name but “Tracker” is such a broad term. This almost feels more like a Transaction Slicer and Dicer.
Thanks again for all your hard work and creativity, @yossiea!
Thanks for the kind words! I will take a look at your suggestions and see what I can do. I know I was looking at some sort of filter to select which columns to include but not sure if that’s allowed. I think we can somehow figure out the tag/note column.
Quickly, with regards to the category filter, not sure what you mean since the category selection does filter out the results on my end.
Good point re: the Category filter… I must’ve have damaged the formula somehow in my testing. Just reinstalled and it worked as expected.
Another minor thing… In my sheet, because my Transactions table extended beyond the data, I was getting weird null results at the end of J4:J.
I think you can fix this with a formula like this (which also has the advantage of not using the Month column which can be unreliable for manual transactions): =SORT(UNIQUE(FILTER(DATE(YEAR(Transactions[Date]),MONTH(Transactions[Date]),1),Transactions[Month]<>0,FALSE)))
You would want to put this in K4: =SORT(J4#,1,-1)
Thanks for that pointer, I made the change and also pushed it out to Z.
I am also thinking of just removing the “note” filter since we don’t really use it and see about putting tags as optional.
I updated the template and marked it as Version 1.02
For the name, I think that Slicer and Dicer is good but many people might not be aware of what a slicer is. Perhaps Review or Analysis or something similar might work?
-edit- I seemed to have fixed the “tag” column, but can’t 100% confirm since I have tags and it does work when I delete but just want to make sure it works. What I did was put an iferror around the filter for Tags column, so if it’s not there, it’s #ref, which then blanks the column.
It looks like it’s still pulling from the template and not your sheet. Right now it will show that if there’s no data. I don’t think I put the error wrapper in yet.
I apologize. That was happening due to the Tag field/filter. I removed it since it’s causing issues and it’s not part of the standard Tiller fields. If you try again, it should work.
Current version is 1.03.
