@lillytryon asked this question in another thread:
Is there a way that you can create a sheet that allows me to choose a category and see the details of transactions for that category only, for a defined period of time? For example, maybe I want to review the gift category to review transactions during December. Or my Reimbursement category (used for expenses that will be reimbursed–employer, health insurance, etc.) to see what I need to submit for reimbursement for the last quarter.
I initially pointed her to the Category Tracker solution, but that wasn’t exactly what she needed reporting-wise.
That’s sort of what I’m looking for. But I need the dates and details (notes, tags) for each transaction. And it wouldn’t be helpful at all if identical descriptions were combined into a single summed line.
Unfortunately, I didn’t know of any other solution that met her needs reporting-wise. The
Category Tracker was close, but not quite there.
After playing around with a copy, I was able to adjust the
Category Tracker solution to display discrete transactions, including date, description, amount, tags, and notes. Aside from the more detailed and discrete reporting, it is identical in usage to the
Category Tracker solution.
You can add a copy of my Detailed Category Tracker solution to your existing Tiller Foundation spreadsheet, and it should be ready to report on transactions.
There is one known issue with the sheet as it is now, and that is that if your
Transactions sheet does not have a
Notes column added, the report will not populate due to the column lookup failing. To resolve this, you can either add a
Notes column to your
Transactions sheet, or you can modify the solution to not include a
Notes column by replacing the formula in cell
D7 with the below formula:
=iferror(QUERY(Transactions!A:AF,"SELECT " &L4& ", " &L1& ", " &L3& ", " &O1& " WHERE " &L4& " >= DATE """ & text(F4, "yyyy-mm-dd") & """ AND " &L4& " <= DATE """ & text(F5, "yyyy-mm-dd") & """ AND " &L2& " = '" & F3 & "' ORDER BY " &L4& " ASC, "&L1&" ASC, "&L3&" ASC", -1),"")
I hope that this helps a few people out!
Thanks, everyone! I really appreciate all of your feedback and suggestions along the way.
I’ve made a new enhancement, adding in the option to query either at the category or group level, still having the option to show a transaction summary or detailed list. This was a substantial enough change that I have created a new template, the Transaction Tracker . Please try it out and let me know what you think.
I made a minor correction on the Transaction Tracker to the Total for Category/Group label in
G8. The header wasn’t changing with the Category/Group dropdown, and the sum wasn’t taking the column drift into account when looking for the Amount column.
If you want to simply copy/paste formulas, you can replace them with the following. Otherwise, you can replace your entire
Transaction Tracker sheet with the template.
="Total for "&G3