Linking register to budget? (spreadsheet)

[Frügal]

I love Google Drive. I use it for so freaking much!

After almost a year with YNAB, I am realizing that I don't understand my finances as well as I did when I did not use YNAB. I am finding myself asking where all our money went, etc. And when I find out, I realize that it is because my budget amounts are not strictly lain-out. YNAB is too "soft".

So here I am attempting to solve this issue. Before, I used PocketMoney. However, you had to purchase the desktop version to accompany the iOS app you already purchased, if you wanted desktop management. Google Drive is free. And I love spreadsheets!

With that out of the way, I'd like to ask some questions. First, here's what my sheet looks like right now:
- first tab is the monthly budget and accounts balances
- second tab is the current month's register
- any subsequent tabs are previous months

Is there any way to link the following:
- 2nd tab's entries to 1st tab's budget categories (so that they will deduct from that category for a running balance)
- 2nd tab's entries to 1st tab's accounts (so that they will deduct from that account for a running balance)
- make it so that any tabs after the 2nd one do not affect the 1st tab's accounts or categories

If I need to consolidate the budget and accounts into a single tab to avoid previous months messing up the categories and accounts, that is fine.

Thanks so much!

edit: Or, maybe, each tab will be each account?
edit: Okay, here are some images.
In the first screenshot, you will see the register. You'll see that there are drop down menus for CATEGORY and ACCOUNT.
In the second screenshot, you'll see a small portion of the budget. I would like the Accounts and Categories to be able to read from the Register. The problem is, I'm not sure how to link them up. Something that will take the Account name, Category name, and the amount, and push it to those cells.
And, the advanced editor isn't working... >.<

[doingitallwrong]

My head is kind of spinning after reading that and I can't see the screen shots (it says there is an error in the file?), and maybe you don't want to hear this, but have you looked at Mint.com? It does exactly what I think you're trying to do, which is automatically assign entries in your checkbook register to your budget categories and keep a running total for the month. You can customize your budget categories and you can change the automatic categorization on transactions, on a one-time basis or every time that payee comes up.

[herdjohnson]

I also can't see any images, but I assume you're trying to do something like this?

Google Docs Budget

And at the end of each month, you'd just right click on the tab, say "Duplicate", and then rename the current tab to the new month. Then clear all the activity off. You can also set up data validation to give you drop down categories and accounts so that you're not typing them in all the time.

[Baby_nurse]

I don't understand where you are getting the idea that categories in YNAB are too soft. You have complete control over the granularity.

[Frügal]

Let me see if the uploader works this time.

As for YNAB, it just hasn't been working. No need to go into it too much, other than it's just not syncing with my style is all.

See the drop-down menus in the screen shot for CATEGORY and ACCOUNT? I want to be able to track the balances of those. To do so, I somehow need to link the dollar amount to CATEGORY and ACCOUNT. How can I do this?

edit: GUH! Screw the built-in uploader. Here:

http://i.imgur.com/erAoL9E.png

http://i.imgur.com/PO3utE0.png

[herdjohnson]

Let me see if the uploader works this time.

As for YNAB, it just hasn't been working. No need to go into it too much, other than it's just not syncing with my style is all.

See the drop-down menus in the screen shot for CATEGORY and ACCOUNT? I want to be able to track the balances of those. To do so, I somehow need to link the dollar amount to CATEGORY and ACCOUNT. How can I do this?

edit: GUH! Screw the built-in uploader. Here:

http://i.imgur.com/erAoL9E.png

http://i.imgur.com/PO3utE0.png

Isn't that what my link does?

[riverwed070707]

I can't see your images either but if I'm understanding correctly, I think you can solve your issue with a couple formulas.

If you aren't already, first you need to total the categories on tab 2 with a conditional formula like: =countif(c2:c132,"grocery") where c2:c132 is the range of cells with your transactions and "grocery" is the name of the category you're totaling. Then you will need to use a formula on tab 1 to carry that total over which is an equal sign followed by the name of tab 2 with an exclamation point and then the cell the total is in (ie: =transactions!C138). You can also achieve this by clicking in the cell you want the total to carry over, inserting an = and then clicking to tab 2 and clicking the cell you want to carry over followed by enter. Then you'll need a new cell that subtracts your budget from the carry over cell.

Hope that makes sense All that said, I agree Mint.com would be much easier.

ETA downside of this is you will need to update your formulas every month to pull from the new sheet.

[herdjohnson]

I can't see your images either but if I'm understanding correctly, I think you can solve your issue with a couple formulas.

If you aren't already, first you need to total the categories on tab 2 with a conditional formula like: =countif(c2:c132,"grocery") where c2:c132 is the range of cells with your transactions and "grocery" is the name of the category you're totaling. Then you will need to use a formula on tab 1 to carry that total over which is an equal sign followed by the name of tab 2 with an exclamation point and then the cell the total is in (ie: =transactions!C138). You can also achieve this by clicking in the cell you want the total to carry over, inserting an = and then clicking to tab 2 and clicking the cell you want to carry over followed by enter. Then you'll need a new cell that subtracts your budget from the carry over cell.

Hope that makes sense All that said, I agree Mint.com would be much easier.

ETA downside of this is you will need to update your formulas every month to pull from the new sheet.

countif will tell you how many transactions you had. sumif will tell you the total of your transactions. And you can just do the sumif formula on the first tab. You don't have to do it on the same tab and then link it over.

[doingitallwrong]

All that said, I agree Mint.com would be much easier.

Just as an FYI, here are a few screenshots of what Mint.com offers. (I should point out that I don't use Mint regularly myself, I don't 'budget' that way, but I do check every so often to see where I've been overspending, I use it to track expenses on my rental property, and I like to see the net worth calculation.) The first one shows how you can change a category on a register item (it was automatically categorized as auto insurance) and set it up so that payee is always assigned to that category. You can do quite a lot of customization.

[Frügal]

I can't see your images either but if I'm understanding correctly, I think you can solve your issue with a couple formulas.

If you aren't already, first you need to total the categories on tab 2 with a conditional formula like: =countif(c2:c132,"grocery") where c2:c132 is the range of cells with your transactions and "grocery" is the name of the category you're totaling. Then you will need to use a formula on tab 1 to carry that total over which is an equal sign followed by the name of tab 2 with an exclamation point and then the cell the total is in (ie: =transactions!C138). You can also achieve this by clicking in the cell you want the total to carry over, inserting an = and then clicking to tab 2 and clicking the cell you want to carry over followed by enter. Then you'll need a new cell that subtracts your budget from the carry over cell.

Hope that makes sense All that said, I agree Mint.com would be much easier.

ETA downside of this is you will need to update your formulas every month to pull from the new sheet.

Wait... What? So I tried countif and sumif and I'm getting errors. I don't see where it is linking to the amount column anyways...?

Can someone just give me an example of them getting this to work?

[herdjohnson]

Wait... What? So I tried countif and sumif and I'm getting errors. I don't see where it is linking to the amount column anyways...?

Did you look at my link above? It has all the formulas already connected...

[Frügal]

Did you look at my link above? It has all the formulas already connected...

I'm not sure how to get that working on my spreadsheet?

[herdjohnson]

I'm not sure how to get that working on my spreadsheet?

just type in "=sumif(" without the quotes, then click over to your register and select the column you want to look up (your category for example). Then type a comma, and go back over to the first sheet and click the category you want to look up (likely the cell to the left), put another comma, and then go back over and select the column with the transaction amounts.

So on mine it would look like: =+SUMIF('July 14'!A:A,Balances!A4,'July 14'!C:C)

judging from your images, yours would be =SUMIF('July 14'!E:E,Balances!D8,'July 14'!D) assuming your sheet names were the same as mine, and D8 on your balances page was "Fuel". Hope this helps. If you still need help, google sumif tutorials on excel... they work literally the exact same way.

[Frügal]

just type in "=sumif(" without the quotes, then click over to your register and select the column you want to look up (your category for example). Then type a comma, and go back over to the first sheet and click the category you want to look up (likely the cell to the left), put another comma, and then go back over and select the column with the transaction amounts.

So on mine it would look like: =+SUMIF('July 14'!A:A,Balances!A4,'July 14'!C:C)

judging from your images, yours would be =SUMIF('July 14'!E:E,Balances!D8,'July 14'!D) assuming your sheet names were the same as mine, and D8 on your balances page was "Fuel". Hope this helps. If you still need help, google sumif tutorials on excel... they work literally the exact same way.

Ah-hah! That worked! Now, how can I get it to subtract from the budgeted amount in Column E on the first tab?

You rock!

[herdjohnson]

Ah-hah! That worked! Now, how can I get it to subtract from the budgeted amount in Column E on the first tab?

You rock!

Just put your budget amount in E and have F as =f8-d8 (or whatever row you're on). Or you can do it vice versa depending on how you want to see it. My way will show a negative when you go over budget in an expense category.