Budget System

[Gabriel Powell]

I'm attempting to create a Budget system in Excel. The first sheet,
named Budget, contains the budget with categories, amounts allowed in
each category, amount spent, and amount available. The second sheet
is a journal-type where I want to enter individual transactions (named
'Journal'). The Journal contains a drop down so each transaction can
be attributed to a Budget category.

What I want to do is link the two sheets together so that when a
transaction is added in 'Journal', the appropriate category in
'Budget' is updated appropriately.

One meager attempt had me making a huge list of IF statements that
would add each row if it had the appropriate category title.
Unfortunately this attempt was swayed by the fact that functions can
only be so long and limited me to to less than 30 transactions a
month.

Any ideas would be much appreciated!!

 

[Dale Hymel]

My first thought was to use the following in cell Column C (Amount Spent) on
you Budget sheet, where A2 thru A??? contains the Categories, and on the
Journal Sheet where B1 thru B??? contains the Budget Categories and C1 thru
C??? contains the amount.

=SUMPRODUCT((Journal!$B$1:$B$14=Budget!A2)*Journal!$C$1:$C$14) . Then Copy
down for all categories

But then if I was planning a budget it would include different months. So
why not put everything on one sheet.
Date Category Amount

Then Budgeted amounts in positive number and journal entries in negative.
Then use a pivot table to see monthly totals.