The Yearly Budget sheets uses Arrayformula, Lookup formulas and Queries to pull data from the Transactions and Categories sheets.
Here's an inside look on how it's done.
Getting the Category List
A query in A6 on the Yearly Budget sheet creates a list of all the categories and types from the categories sheet using the formula:
=QUERY(INDIRECT(BG18),"SELECT "&BH9&","&BH10&" WHERE "&BH12&" <>'Hide' ")
Since columns might be ordered differently in your sheet, the column references are dynamically calculated in column BH and then used in the query. An easier to read version of the query would be:
=QUERY(Categories!B:D, "SELECT Category, Type WHERE Hide From Reports is not 'Hide'")
This removes Categories flagged as "hide" in the Hide from Reports column on the Categories sheet.
Monthly Budget Amounts
The monthly budget amounts on the Yearly Budget sheet come from 12 consecutive columns in the Categories sheet. The header for the January budget must be labelled "Jan". This enables the column # to be calculated in cell BG13 using the formula:
The "Jan" column letter is calculated in BH13 using:
By adding 11 (for 11 months) to the "Jan" column, the December column is calculated in BG14 and BH15. The range for the Budget lookups (which will be shown below) is calculated in BG20, from the Category column (calculated in BH9) to the December column (BH14).
Once we have this range, we can VLOOKUP the budget values for each category for each month. The formula for January is:
This looks up the Category name in Column A and returns the value in the Jan column on the Categories sheet. If there is no Category name in Column A, "" (blank) is displayed.
Monthly Actual Amounts
A query in AR6 creates a pivot table by month for the sum of transactions by Category from the Transactions sheet for the chosen year in B2 of the Yearly Budget sheet.
=query(Transactions!A:Z,"SELECT "&BH5&",SUM("&BH6&") WHERE "&BH5&" IS NOT NULL AND YEAR("&BH4&") = "&B2&" GROUP BY "&BH5&" PIVOT MONTH("&BH4&")")
This pivot table is then used to fill in the Actual monthly data. The formula for January, in F7, is
=ARRAYFORMULA(IF(A7:A<>"",IF (ISNA(VLOOKUP(A7:A,AR:BE,AQ4,FALSE)),0,IF(B7:B="Expense",-1*VLOOKUP(A7:A,AR:BE,AQ4,FALSE),1*VLOOKUP(A7:A,AR:BE,AQ4,FALSE)) ),""))
The formula returns "0" if the Category name in column A is not found in the pivot table. This could happen if there were no transactions for the Category. If the category is an expense, the negative sum is converted to a positive number. If the category type is Income, the sum remains a positive number. If no name is found in Column A, blank cells are displayed.
All the yearly totals for Actual, Budget, and Available columns are calculated by summing the individual months.
Google Support articles about the following built in features of Google Sheets used in this template: