Docs: Monthly Budget Calendar (Excel)

Spreadsheets Excel Templates
, , , ,
21

I am having the same problem I think. It isn’t displaying the values from my spreadsheet in the calendar. Any suggestions?

22

Sorry to hear, @gdwaldon.

Often the best first step to debugging a template issue is to unhide the “hidden area” (columns) that is often to the right of the dashboard contents. You can do that by clicking the small arrow in the top-right-most column header. There, you will see the lookups and calculations that feed the data into the dashboard. Usually if there is an issue with data flowing from your spreadsheet into the dashboard, you will see broken formulas or empty cell references (in lookups) here. Can you peek at the hidden area in your sheet and let us know if anything looks amiss?

Randy

23

I just installed this template after using Tiller for a few years. I actually installed the Google Sheets version but there is no “Reply” available on its doc sheet so I’m replying here as I suspect the same would apply to the Excel version.

This is pretty cool, but I had one issue that was making the data somewhat meaningless in my situation. I have a lot of transactions that are not transfers, but I don’t want included in my “regular” expenses. These are mainly investment activities that take place in managed brokerage accounts. On the Categories sheet I have these marked as Hidden From Reports. Therefore, I wanted the Monthly Budget Calendar to not include transactions for all hidden categories.

There may be more elegant ways of doing this, but my quick and dirty solution was to add a calculated column to the Transactions sheet and then filter on it in Monthly Budget Calendar. Here were my steps in case anyone else has this same desire (note: cell locations may vary for the Excel version):

  1. Transactions Sheet – Add new column with the following formula: =arrayformula(if(row($D$1:$D)=1,“Hide From Reports”,iferror(vlookup($D$1:$D,{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),“”)))
  2. Monthly Budget Calendar – Add “Hide From Reports” to P11 and copy Q10, R10, and S10 to Q11, R11, and S11 respectively.
  3. Monthly Budget Calendar – Update the formula in W7 to filter out the “hidden” transactions by adding AND Col4 <> ‘Hide’ to the WHERE clause of each QUERY.
1 Like