In upgrading to the new Tiller Labs Business Dashboard, I note the sheet does not appear to be filtering out hidden categories. How do I correct this?
Thanks!
This might not be the best way to do it, but I was able to get this working:
Add a new Hidden column to the Transactions sheet:
=arrayformula(if(row(C1:C)=1,"Hidden",iferror("Hide"=vlookup(C1:C,{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("Hide From Reports",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Hide From Reports",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))
Unhide the hidden columns and rows on the Dashboard, then add a new column after the other defined columns for Transaction Addresses. For me, I added a new column after Group.
Fill in Row 2 next to Group:
Hidden
Highlight the calculated rows 3-6 in the Group column and drag the bottom-right corner to fill in the formulas in your new column.
Your new column should look something like this:
Hidden
11
$K$2
$K$2:$K
Transactions!$K$2:$K
Replace the the first Income formula in the Income and Expense section with this:
=if(isblank($AF11),iferror(1/0),if($M$23="All Transactions",sumproduct(indirect($Y$6)*(indirect($Y$6)>0)*(indirect($AB$6)>=$AF11)*(indirect($AB$6)<eomonth($AF11,0)+day($AF11))*(not(indirect($AE$6)))),sumproduct(indirect($Y$6)*(indirect($AB$6)>=$AF11)*(indirect($AB$6)<eomonth($AF11,0)+day($AF11))*(indirect($AA$6)<>"")*(indirect($AA$7)=$AH$10)*(not(indirect($AE$6))))))
You want to make sure where my formula says $AE$6 points to the cell containing Transactions!$K$2:$K. You’ll need to replace both references in the formula.
Highlight the cell you just updated and drag the bottom-right corner down to update the formula for the rest of the rows in the Income and Expense section.
Do the same steps you just did for the Income column with with Expense column. Here’s the formula for the first row:
=if(isblank($AF11),iferror(1/0),if($M$23="All Transactions",-sumproduct(indirect($Y$6)*(indirect($Y$6)<0)*(indirect($AB$6)>=$AF11)*(indirect($AB$6)<eomonth($AF11,0)+day($AF11))*(not(indirect($AE$6)))),-sumproduct(indirect($Y$6)*(indirect($AB$6)>=$AF11)*(indirect($AB$6)<eomonth($AF11,0)+day($AF11))*(indirect($AA$6)<>"")*(indirect($AA$7)=$AI$10)*(not(indirect($AE$6))))))
Your Income vs Expense chart should no longer include transactions in hidden categories!
, @sarah_t_rex!
I’ll pass this to our Labs team to see if they can make sure the hidden categories are filtered out.
Thanks for the solution @richardpeng!
Heather
I just upgraded to the latest Business Dashboard and see that a lot of things have changed. It’s now much easier to filter out hidden expenses. You no longer need to add a Hidden column to Transactions.
You just have to update the two cells that say Query Month: V5 and X5:
V5:
=query({INDIRECT($BB$19),INDIRECT($BB$18),INDIRECT($BB$17),arrayformula(vlookup(INDIRECT($BB$18),{INDIRECT($BB$4),INDIRECT($BB$5)},2,false)),arrayformula(vlookup(INDIRECT($BB$18),{INDIRECT($BB$4),INDIRECT($BB$6)},2,false))},"SELECT MONTH(Col1)+1, SUM(Col3) WHERE "&if($K$16=$Y$2,"Col4='Income' AND Col5<>'Hide'","Col3>0")&" AND Col1 >= date '"&TEXT($X$1,"yyyy-mm-dd")&"' AND Col1 <= date '"&TEXT($Y$1,"yyyy-mm-dd")&"' GROUP BY Month(Col1)+1 ORDER BY MONTH(Col1)+1 ASC LABEL MONTH(Col1)+1 'Query Month', SUM(Col3) '"&if($K$16=$Y$2,"Income","Inflows")&"'")
X5:
=query({INDIRECT($BB$19),INDIRECT($BB$18),INDIRECT($BB$17),arrayformula(vlookup(INDIRECT($BB$18),{INDIRECT($BB$4),INDIRECT($BB$5)},2,false)),arrayformula(vlookup(INDIRECT($BB$18),{INDIRECT($BB$4),INDIRECT($BB$6)},2,false))},"SELECT MONTH(Col1)+1, SUM(Col3) WHERE "&if($K$16=$Y$2,"Col4='Expense' AND Col5<>'Hide'","Col3<0")&" AND Col1 >= date '"&TEXT($X$1,"yyyy-mm-dd")&"' AND Col1 <= date '"&TEXT($Y$1,"yyyy-mm-dd")&"' GROUP BY Month(Col1)+1 ORDER BY MONTH(Col1)+1 ASC LABEL MONTH(Col1)+1 'Query Month', SUM(Col3) '"&if($K$16=$Y$2,"Expense","Outflows")&"'")
@richardpeng and others,
There is a new update to the Business Dashboard that better respects (and removes) transactions from hidden Categories. You can get the update from the Tiller Labs add-on and then use Manage Solutions to Upgrade the Sheet.
Tech info: Formulas in cells
AA5, V34, V5, X5, AJ2 and AF2were adjusted so the queries don’t include transactions where the Category is hidden in the Categories sheet.
Note that when selecting the Filter: All Inflows vs Outflows, hidden category transactions are not removed. All Inflows vs Outflows does NOT take category information into account.
Thanks folks! Appreciate the assistance/update.