Budgeting macros

**bibleguy125** · 11-14-2009, 05:17 AM

Okay so here's my plan. I already have an excel sheet that acts as an electronic envelop system. The problem is that you have to enter each item in manually and it is spread out across three different worksheets within the workbook. It's clunky and time consuming.
Here's what I want to do. I have an allocation system that I made that let's me put in a given amount of income and spread it out throughout my different budgeting categories. I want to put the 'envelops' right next to it so that everything is on the same page, then have a macro that will let me get the allocations set how I want, then submit them to add or subtract the allocated amounts from the envelops. It seems very simple, but I don't know much about macros. I'm pretty good with formulas, but can't get this to work without a circular iteration, and I have a hunch that this is macro territory anyway.
Can anybody help me figure out the macro for this? There is a copy of what I have been working on attached. Thanks guys!

**JBeaucaire** · 11-14-2009, 01:22 PM

Can you explain your process?

For instance:
1) Enter a $ amount D3
2) $ amount entered parses itself out to the table in column B based on percentages already entered into each cell.
3) Something else appears in column D?
4) Pressing SUBMIT enters the amounts that appeared in the table to the various envelopes and table resets

Can you provide all of the "percentages" for the table? Fill out a sample entry amount with all the values, perhaps based on a $100 total allocation.

Also, if you make sure the contents of the "envelope" names exactly matches the table, then a macro can find those cells whereever you move them to in the future allowing you to have flexibility with your layout. That means no hard-coded returns. Just put "Children: Diapers" into both the table and the envelopes.

If you want wrapping on the envelopes, set WRAP TEXT in the format cells for those cells.

**bibleguy125** · 11-14-2009, 01:54 PM

As I said, I'm not all that familiar with macros, so you might have to explain exactly what you mean by:
"Also, if you make sure the contents of the "envelope" names exactly matches the table, then a macro can find those cells whereever you move them to in the future allowing you to have flexibility with your layout. That means no hard-coded returns. Just put "Children: Diapers" into both the table and the envelopes."
As far as the process, my income is hourly, so it changes every month, therefore I can't just put in percentages for my non-discretionary expenses. The only category that has a percentage already put in is the offering category.
How it works is
1)I would put in the amount of income in B3
2)the offering category automatically inputs 10% and I allocate the rest of the income throughout as I need it
3)D3 displays the subtotal of B3-SUM(B4:B26)
4)I then allocate the discretionary categories as I need them
5)D26 gives me the remainder of the subtotal left to allocate and let's me know how much I have left to allocate - when it reaches zero, I'm done
What I would like to happen is
1)After I get everything allocated how I want it, I would hit the submit button and it would send the amounts I have allocated to the proper 'envelope' on the right and sum it
I used to just use the allocations for income and have basically a register where I could put in the expenses and off to the right had a column for each category that would sum it all and display the result at the top.
That was a lot of work and took a lot of time.
I figure I can just use the allocations for income and expenses, allocate them throughout the categories, and submit them into the proper envelopes.
You'll have to explain things very thoroughly, because I don't know much about macros, all I have done so far is record macros to reset forms.
Thanks for the help!

**trucker10** · 11-14-2009, 06:11 PM

3 x reads, but I do not understand

**JBeaucaire** · 11-15-2009, 12:39 PM

To make your sheet "flexible", I added some "location" columns where you can indicate where the envelopes are located. You'll see them when you open this sample sheet. If you find these helper columns unsightly, just hide those columns...but don't forget about them! This will allow you to restructure your table anytime and any way you wish and the macro will kee working.

So your Allocations sheet should now function the way you want.
=========

However, I've added a second sheet showing a little more standard and basic layout. It makes the update process far simpler as you can see by the difference in the macros for each of those two sheets.

So you have two working options.

**bibleguy125** · 11-16-2009, 10:13 PM

That is great!
Okay, now say I wanted to do monthly updates instead of each individual transaction. How would I go about doing that?
I use Quicken to keep track of finances, but it doesn't do envelope stuff, so I want to be able to update whenever I get the chance, but not have to put every transaction in. So basically, I'll update every so often, but the totals will be monthly instead of each transaction, my Quicken program gives me tables of month to date expenses, so whenever I update, I'll just put those totals in instead of each individual transaction.

**JBeaucaire** · 11-17-2009, 01:39 AM

I don't see how it's any different. You'll still take the "totals" from your other program and insert them into the various categories, then SUBMIT to record them permanently.

**bibleguy125** · 11-20-2009, 10:43 PM

That would be true except that I want to be able to submit several times a month, but have it still keep track of monthly totals instead of each individual total.
So say on the 2nd of the month, I use $2 from groceries, so I submit it and it takes $2 out - then on the third I take out another $2
My Quicken program will tell me that I have taken out $4 for the month so far, so instead of having to keep up with each transaction, I can update whenever it's convenient. So I would put in $4 for the month on the 3rd, and it would take another $2 out of the 'envelope'.
I know this is somewhat complicated, just an idea I had that I don't know how to program. Thanks for all the help!