Monthly/Yearly Budget and IF statements

Hello All,

I'm rather new to using Excel. My wife and I created a monthly/yearly budget in Excel tailored to what we need. However, we seem to have many problems with what we would like Excel to do...

Basically I have 12 worksheets, labeled Jan08 - Dec08. Each worksheet is the same. There are several categories of budgeted information with three points of data in them- "Budgeted"(a1), "Spent"(b1), "Remaining"(c1). Say for instance, I have $150 budgeted in each months worksheet for "misc." Throughout the month I enter in how much I spend on that category, it tells me the remaining amount I have left. What isnt spent for that category is set aside in savings, earmarked in a "misc." fund. This is the same logic I use for all categories and expenses for all months. The "remaining" cell formula is =sum(a1-b1).


Here's the problem:
On the "summary" worksheet I'd like a running tally of what is in each "fund", to tell me how much money is left, for the year, on that particular budgeted item.
For instance, if it's March and I havent spent anything on "Misc." the "fund" value should be the budgeted amount of each prior month, or, $450.00. If I have spent something, then the value should reflect that as well.

I've tried several ways of doing this:
a.) Creating a formula with a Defined Name that reflected each "remaining" cell value for each month/worksheet and on the summary worksheet having a formula like this =sum(misc.remaining). This didnt work because it calculated all remaining months worth of budgeted money- i.e., in February it told me I have 1800.00 in the "fund" (150.00 x 12 months)

b.) Creating a drop down list with each month in one cell and based on that cell's value a formula would reflect what I needed:
=IF(K5="January",'Jan08 '!L26,IF(K5="February",Feb08!L26,IF(K5="March",Mar08!L26,IF(K5="April",Apr08!L26,IF(K5="May",May08!L26,IF(K5="June",Jun08!L26,IF(K5="July",Jul08!L26,"")))))))

However, I didn't know that only 7 IF statements could be placed in one statement.


I'd be happy to explain this more in detail and/or provide what I've come up with so far if someone can help. I don't know anything about VB, so creating a VB function for my question is beyond me.

Any help would be appreciated!

Hi

I think use of 'Consolidate function' as mentioned below.

From Menu Select
Data -> Consolidate -> Select option 'Sum' -> Select Range -> Do not forget to check box for 'Ceate links to source data'

Also see attached file for help.

thanks vandanavai, however- I would still need a running total of the amounts in question....

If it's March I just need to see what's left over in the budget for a specific category (Misc) from Jan08 and Feb08, then add that amount to the allocated budget for Mar08. If it's May, the formula would have to adjust itself to reflect the change in time....

It was a good tip though, something I did not know! thanks for the advice!