Expense Worksheet Questions

[joeeoj]

Hi Everyone!

I'm trying to make a workbook (Excel 2007) to keep track of all my expenses & budget and I have a few questions that I can't seem to figure out:

First, how can I make a cell (lets say I6) to display the last used cell in column F? In other words, column F is my running account balance, and I would like a cell at the top of the sheet (I6) to display very clearly my current balance at any given time without having to scroll down to find it. Is this possible?

And the more complicated question I have: Is there anyway to make a chart embedded in a worksheet that changes when a certain drop down menu is chosen. By this I mean I have entries for each expense that has the month in Column A, type of expense (car, rent, gas, etc) in Column D and cost in Column E. What I want is to have a drop down menu (say in cell J6) that has each month listed. When I click 'July' for example, I would like the chart to display a pie chart containing all the expense types (car, rent, gas, etc) for that month as a cost percent. The key is, when I change it to another month I want it to REPLACE the existing chart.

Hopefully I explained this somewhat decently...

Thanks in advance!!

 

[pedro-egoli]

First, how can I make a cell (lets say I6) to display the last used cell in column F? In other words, column F is my running account balance, and I would like a cell at the top of the sheet (I6) to display very clearly my current balance at any given time without having to scroll down to find it. Is this possible?

This formula was provided to me in answer to a similar question I posed.

Assuming range you want is F2:F1000 try this

=LOOKUP(9.999E+307,F2:F1000)

Can't help with the chart query though.

Pedro

 

[R Pelle]

I was playing around with your questions and came up with the following based on Excel 2007.
A1= Curr Balance
C1= sum formula
A3=Date
B3=Cat
C3=Amount
Step 1:
Then I converted the range to a table and renamed the table to "ExpenseTable"
Step 2:
Create a pivottable based on the table and grouped the date by Month & Year.
Sum of Amount
Years, Date,Cat,Total
2011,June,Car,$-300
etc

Step 3:
Create a Piechart based on the Pivot table.

Step 4:
To view e.g. June 2011 in the Pie, change the filter to display June in the pivottable and you (pivot)chart will display June automatically.
Sorry for the crappy code, I cant use Excel Jeanie (at work).

Rob

Current Balance       =SUM(ExpenseTable[[#All],[Amount]])
 
Date        Category    Amount
01/05/2011  Balance   $2,500.00
05/05/2011  Car       $-300.00
09/05/2011  Rent      $-800.00
13/05/2011  Misc      $-100.00
17/05/2011  Food      $-250.00
21/05/2011  Gas       $-75.00
25/05/2011  Salary    $1,800.00
01/06/2011  Car       $-300.00
05/06/2011  Rent      $-800.00
09/06/2011  Misc      $-125.00
13/06/2011  Food      $-300.00
17/06/2011  Gas       $-125.00
25/06/2011  Salary    $1,800.00
01/07/2011  Car       $-300.00
06/07/2011  Rent      $-800.00
11/07/2011  Misc      $-125.00
16/07/2011  Food      $-300.00
21/07/2011  Gas       $-125.00
26/07/2011  Salary    $1,800.00
02/08/2011  Car       $-300.00
06/08/2011  Rent      $-825.00
10/08/2011  Misc      $-250.00
14/08/2011  Food      $-285.00
15/08/2011  Gas       $-150.00

 

[jevi]

Dear Pedro,

Thank you so much . I just posted a question about it and while i was reading your reply i found the question to my problem.

My question was that not F2:F1000 but was F50:Q50 so I wanted the total column to include the last value of the month so I used the same formula and it worked great so i did it: =lookup(9.999E+307,F50:Q50) and super .

have a nice day. thnx a lottttttttttt

 

[pedro-egoli]

Thanks and glad it helped solve your problem.

This is the thread where I was informed, and you will note a bit of an explanation as to how it is calculated.
http://www.mrexcel.com/forum/showthread.php?t=463551

Pedro

 

[R Pelle]

So.... were you not interested in the dynamic piechart?

Rob

 

[jevi]

Ah i see..yeah good information . But it was fun that i just post my thread 5 minutes before i found your reply to this thread and I thought that is the solution.

@Rob...are you refering to me?! I was not interested in a pie chart as my work is not like a budget expenses but is an evaluation of sales and non performing loans etc...! Bur for sure I might need your formula in a second moment .

Ciao ciao

 

[R Pelle]

@ Jevi, hmm my mixup, I thought you were the OP. He apparently has not bothered to respond yet, or has gone on holidays
Rob

 

[joeeoj]

I'm so so so sorry for not responding quicker..

I posted this question two nights ago thinking I would be able to wake up and respond the next morning but for whatever reason these last few days have been very hectic and I haven't been able to work on this project at all. So my apologies - I would never disregard someone taking the time to help me.

Back to my problem - after spending an hour or so tonight I was able to get to get both suggestions working and am super happy with the results! They both did exactly what I originally asked so thank you for all the input!

I'm sure as I progress on this project / get a little more fancy with it, I'll be back asking for a little more help.

Thanks again everyone!!

 

[R Pelle]

You're forgiven , thx for the feedback.

Rob