Account and Category Report

[Cowboy13]

What is the goal of your workflow? What problem does it solve? How does it help you?

Goal is to be able to pull a Transaction by many different criteria- time period, account, category, account group, category group and be able to summarize and sort by different criteria.

I think (it’s been a while) I started with the Category Tracker Report (https://www.tillerhq.com/community-solutions/category-tracker-report-for-google-sheets/), but wanted more flexible…

How did you come up with the idea for your workflow?

Started with the Category Tracker and customized.

What are the sheets included with your template? Does your workflow use any custom scripts or formulas?

Only the 1. There are no custom scripts or formulas.

Is it ok for others to copy, use, and modify your workflow?

Absolutely. Here is the URL Account and Category Tracker

Set up and use

Download and use.

Corner Cases, Error Handling (what should happen ), or Limitations

None that I am aware of.

Misc

Enjoy and let me know if you have any questions.

Scott

[KirkO]

Hi Scott. I ran across your ‘Account and Category Report’ and I think it’s exactly what I need. However, it’s not working for me. I copied this into my main Tiller Google Sheet. It’s picking up the categories and groups, but nothing is displaying in the main tabular area. I noticed in the ‘working area’ (orange headers), there are several errors. i.e. ‘Helper Column’ and ‘Summarized By’. shows #N/A.

Perhaps you can submit this to this to the ‘Tiller Community Solutions’ builder contest, so it can be reviewed, then possibly added to the Community Solution add-on sidebar?

Thanks!

[Cowboy13]

Not sure “Group” would be NA when all the others are populating correctly. Can you check the Group formula in Columns O and P to make sure it is consistent with the other Transaction formulas?

Try “All” in the report section (anything but “Group”), and see if the orange “Sort” section populates correctly.

Also, the “Helper Column” (column AD) should not be N/A. This is simply finding columns AU-BE to the right…

Thanks,
Scott

[KirkO]

@Cowboy13 thanks for the quick reply. I think I get what your asking. In the O & P columns, under ‘Group’ (row 8) this is the formula I see:

O:
=if(or(isblank(M8),isblank(N8)),iferror(1/0),REGEXEXTRACT(ADDRESS(1,Arrayformula(SMALL(IF(indirect(M8&"!A1:1")=N8,Column(indirect(M8&"!A1:1"))-Column(INDEX(indirect(M8&"!A1:1"),1,1))+1),if(or(M8&N8=“AccountsAccount”,M8&N8=“AccountsGroup”),2,1)))),"[A-Z]+"))

P:
=if(isblank(M8),iferror(1/0),"’"&M8&"’!"&O8)

From my uneducated perspective, these formulas look to be similar to the formulas above and below. I haven’t touched anything in this sheet. I just literally copied it to my main Tiller Google Sheet.

In the main report area, I tried selecting ‘All’ and the orange sort section does not populate correctly. The screenshot I attached was with ‘All’ selected.

Also, for reference, I have attached the Header area of my Transactions sheet.

Thanks again,

[yossiea]

I’m not sure why I didn’t see this before, but this sheet is amazing! The only thing I can improve on is fixing the cells so amounts don’t get cut off, but the usability and need for this sheet is great!

[Cowboy13]

KirkO,
You are missing the “Group” column in your Transaction sheet, which may be causing all the issues. I thought this was a core column for the Transactions sheet, but maybe it was something I added a while back.

Do me a favor- insert a new column in your Transactions sheet and put this this formula in Row 1 of that new Column…

=arrayformula(if(row(E1:E)=1,“Group”,iferror(vlookup(E1:E,{indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH(“Category”,Categories!$1:$1,0),4),1,"")),indirect(“Categories!$”&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH(“Group”,Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

This should populate that column with the Category Group and may fix the issue, but please let me know.

I need to check to see if Group is a core Transaction column or something people add. If it’s something people add, I will need to update my sheet to take that use case into consideration.

Thanks,
Scott

[Cowboy13]

yossiea,
Thanks.

Not sure what you mean by “amounts don’t get cut off”. Can you clarify and send me a screen shot?

-Scott

[yossiea]

Never mind, I don’t see it happening anymore, so either a glitch or it was something I did that caused it.

[Cowboy13]

Thanks for checking. Let me know if you see anything else that’s odd.

[KirkO]

@Cowboy13, that was it! Good catch. Thank you!

‘Group’ is not a core column in the ‘Transactions’ sheet. Since this does not exist, by default, can you pull Group data from the ‘Categories’ sheet instead? Otherwise, everyone would need to add the Group column with that formula like you described.

[yossiea]

I use this formula in my transaction sheet to pull group. I find it’s a little easier to edit if I need, you just change the column header which is simple enough.

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))

[KirkO]

Hi again @Cowboy13. I was wondering if you would be able to add ‘Tags’ from the Transactions sheet as a Filter or ‘Summarize By’ option? I have a few rental properties, and use a single Category for expenses (i.e. Rental-Lawn Care), but a unique Tag for each property (i.e. 123 Main St.) for tracking. When it comes to reporting, especially for taxes, I need to see a break down by property. Essentially I need to summarize by Tag, then by Category (in Excel I think this would be easy with a Pivot table.) Right now I have to use 2 different reports to get this – the ‘Tags Report’ and the ‘Category Roll-up Report’, neither of which are ideal. And the Category Roll-up report does not have a way to summarize categories like your report. So it would be nice to see all expenses, by category and tag, within a single report. Thanks again!

[Cowboy13]

KirkO,
I updated the spreadsheet to pull the Category Group from the Category sheet.

-Scott

[Cowboy13]

KirkO,
I thought about this when I designed the sheet, but instead chose to modify the Tags report to include summarizing by Category for any Tag (or all Tags)…

If you’re interested, I can share this sheet.

-Scott

[KirkO]

Scott, YES, I’m definitely interested in the modified Tags Report. If you can share that, I’d be so grateful. Thank you for all your help and quick replies! I’m really starting to like this whole Tiller Community thing.

BTW, I think these 2 sheets would be great additions to the ‘Tiller Community’ add-on/sidebar. I’m certain I’m not the only one looking for such reports.

[Cowboy13]

Here’s the link to my modified Tags report

As for Add-on/sidebar, I don’t make that call.

-Scott

[yossiea]

I just downloaded the template, but when I select Custom Date Range, I don’t see an End Date, only start date.
Also, it would be nice to have predefined time periods.
Also, Column E loses formatting at row 100.

[Cowboy13]

yossiea,
Neither of these items happen on my end- End Date shows up when choosing custom and formatting goes to the last row of the sheet (which is 4219)…

And, there are multiple pre-defined time periods; but they won’t show up unless you have transactions in those time periods…

-Scott

[yossiea]

Sorry, my mistake, I am referring to the tag report.
-edit- I was receiving an error on the Category/Account report so I figured that’s my doing, so I downloaded the template again, and I am getting array error, E11 doesn’t fill because it would overwrite the dates in S, so not sure what is up since I just downloaded a fresh version.
-edit2- I made a copy of yesterday’s Tiller sheet and the Category report worked fine. I then copied it into my current sheet and it stopped working. So it’s something weird.
-edit3- Figured it out. This morning I used Tiller to add a manual transaction to the sheet. That overwrites the Type column in Transactions, so when you’re searching for Type on your report, there’s nothing there until I delete what Tiller put in and the formula then puts the Type back into all the transactions. You might want to use Type from the Categories sheet so this won’t happen if people don’t have Type on Transactions or it gets overwritten.

[Cowboy13]

I wasn’t planning to share my modified Tags report so it hasn’t been thoroughly tested. I don’t use the report much so I hadn’t seen that time frame error; but I see it now.

It could pretty easily be updated using the same time frame pull down technique I use in the Accounts and Category report. I don’t have an immediate need for this and am buried with other things so won’t get to it for quite a while. If you update it, please let me know and I’ll test it out.

-Scott

-Scott