Cell Update from another sheet

G

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!!
 
P

Phil R

I'm not an Excel MVP, but here's a direction to look at. Set up a pivot
table on a third worksheet. Go to the "Journal" worksheet, then go to Data
menu, Pivot Table and Pivot Chart, and follow the Wizard, Summarize the data
on by Category and put the Pivot Table on a new worksheet.
On the Budget worksheet, use a VLOOKUP formula to pull the summarized data.
You'll need to use an IF statement before the VLOOKUP to trap for errors.

An alternative would be to use SUMIF on the Budget worksheet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top