The Category Tracker Report is terrific but how would I modify it to filter the results to show only one or more selected Accounts?
For example, assuming I have 5 Accounts in column F of the Transactions tab, I’d like to be able to select Accounts 1, 2, 5 to filter the results shown in the Category Tracker Report.
Any help would be appreciated.
I’m not sheet savvy enough to share how you might modify it to do that. I wonder if @jono or @randy has ideas?
Hi @ScottC ,
Here’s how you can modify the Category Tracker report to filter by an Account.
You need to add a lookup for the Account column, get the range of that column and then filter the query in L12 with the selected account,.
Here’s the details:
Open the hidden part of the sheet and add fill down L9:O9 one row to the 10th row.
In L10: Change Categories to Transactions
In M10: Change Hide From Report to Account
This will lookup the column for Accounts in the Transactions tab. If you move your Transaction columns, the formula will still work.
You need a cell somewhere to put the Account name you want to filter by.
As an example, let’s use F5.
Then, you will need to change the formula in L12 to add the Account column to the query and filter by the account name.
Change:
=iferror(QUERY({INDIRECT(O3),INDIRECT(O4),INDIRECT(O5)},"SELECT Col1, SUM(Col2) WHERE Col1 <> '' AND Col3 >= DATE """ & text($E$2, "yyyy-mm-dd") & """ AND Col3 <= DATE """ & text($G$2, "yyyy-mm-dd") & """ GROUP BY Col1 LABEL SUM(Col2) '', Col1 ''"))
To:
=iferror(QUERY({INDIRECT(O3),INDIRECT(O4),INDIRECT(O5),INDIRECT(O10)},"SELECT Col1, SUM(Col2) WHERE Col4 = '"&F5&"' AND Col1 <> '' AND Col3 >= DATE """ & text($E$2, "yyyy-mm-dd") & """ AND Col3 <= DATE """ & text($G$2, "yyyy-mm-dd") & """ GROUP BY Col1 LABEL SUM(Col2) '', Col1 ''"))
Notice the added INDIRECT(O10) and the added Col4 = ‘“&F5&”’ AND
This should filter by one account.
Filtering by multiple accounts makes this more complicated. You would need to select which accounts and add them to the filter query. Same idea as filtering by one account.
Does that help?
Jon
@jono (and @Heather), Yes. It helped and thank you very much. I followed your excellent directions and it worked perfectly.
I don’'t need to filter the results by more than one account but what if I wanted to filter by one account OR all Accounts (e.g. no Account filter)?
Simply our of curiosity, would it be possible to make the formula in L12 dynamic based on the value in a checkbox field or ‘Yes’ ‘No’ field? “Filter by Account?”.
When “Yes”, L12 would refer to the new formula. When “No”, L12 would refer to the original formula.
Thank you again.
ScottC
@ScottC ,
I’m glad you were able to follow along and get it to work.
To make the formula in L12 dynamic, you could add an IF statement with ISTEXT() so IF any text is in the Account cell (for example F5), then include the filter part. If there is no text in F5, then don’t filter by the account.
More on ISTEXT() here: ISTEXT - Google Docs Editors Help
This would save you from having the checkbox field. But the checkbox option could work as well. If TRUE (check box is checked), then add the filter. If not, don’t filter by account.
You might take a look at how the Category “Hide From Reports” is used in lots of Tiller sheets.
On many sheets, categories are only included if they are not hidden based on their settings in the Categories sheet. A VLOOKUP() is used in the filter queries to check whether a category has a Hide in the Hide From Reports column. And if it does, that category is filtered out when the query is run.
To filter by multiple accounts, you could do something similar, but by using accounts not categories.
Jon
