How to make change to categories sheet

In the new foundation template you have to change your annual budget (with no totals) in the categories sheet, and the yearly budget sheet (with the totals), won’t allow you to edit. That’s fine, I think to myself; I’ll just add a row to the top of the category sheet and total things manually on the category sheet.

except…when I add a row at the top, it seems to break all kinds of things everywhere. Is there a way to adjust to give us a row or two at the top for our own totaling purposes?

Thanks!

Hi @swhowiedash,
I would not recommend trying to add a row at the top of the Categories sheet.

The foundations template uses many formulas that rely on searching the first row of the Categories sheet for the Column names. If you put a row on top, that is going to break many of the sheets.

As an alternate, you could put some formulas to the right of the existing columns in the Categories sheet. That would not cause everything to break.

Would that work for what you are trying to do?

Unfortunately, what I want to do is put total income and total expenses amounts for each month, so off to the right wouldn’t really be helpful. I could possibly put at the bottom, but that would involve scrolling all the time to see as well. I was hoping there was a way to get this to populate better as it is the only real block to easily doing multi-month budgeting, imho.

Here’s what you could try. I’m not 100% sure it will work, but it’s worth a try.

Put your total formula in Row 2. That won’t break the formulas and lookup that happen in Row 1.

You might select Type Transfer, or maybe just keep it blank. You might also select Hide from Reports.

There might be some negative side effects by doing this, but on first glance, I think it could work.

Let us know what you find out.

Thanks, jonorlin - that’s a clever idea. I will give it a try and report back.

I’m curious why not just using the Yearly Budget sheet as a planning tool to review the totals and cash flow, then just edit specific months in the Categories sheet to fine tune your budget. Maybe I’m missing something. :slight_smile:

Heather, as my wife and I are filling out and adjusting our zero balance budget for the month, I find myself switching back and forth 10-20+ times as we negotiate category amounts to see how my changes are affecting our budget totals. In the old monthly budget, we could see the changes reflected at the top almost immediately without having to change views. In addition, the category sheet just doesn’t look quite the same as the yearly budget. I know I’m splitting hairs about most of this, but it is an adjustment to switch back and forth and we want the tool to not get in the way of our process. If I can get the totals at the top of the category sheet (still working on the sumif or what it will take to get the type of expense to be negative for zero sum) that would go a long way toward a working, annual budget nirvana. The annual budget sheet is beautiful and we will still look at it for an overview, but we just can’t work from it.

@swhowiedash.
If the Column name is in row 1, and you put the formula in row 2, have you tried this formula:
=sum(E3:E)
(example for Column E)
I’m not sure what you mean by “get the type of expense to be negative for zero sum”.
To switch from a positive to a negative number or a negative to a positive, try:
= -1 * sum(E3:E)

Well in the categories sheet, all numbers, whether expense or income, are listed as positive numbers. To do a zero sum calc, you either have to tally the expense numbers separately and subtract from the income total, or I figured I could do what I do in a database, i.e.
x = x + iif(C=“Expense”,0-E,E)
for each row in the E column

  • this would add the income and subtract the expenses. A balanced budget would come to zero.

Try this:
Sum the Income Categories and subtract the Sum of the Expense categories in the column.

=sumif(C3:C,"Income",E3:E)-sumif(C3:C,"Expense",E3:E)
Where C is your Type column [Income or Expense]
And E is the Column you want summed.

To fill this cell across, and force the C column to remain constant:
=sumif($C3:$C,"Income",E3:E)-sumif($C3:$C,"Expense",E3:E)

Brilliant! - That summed my columns perfectly, thanks!

1 Like