Really need some help with trying to figure things in workbook

[rutledgec]

Hi. I am new and still learning Excel. I have a checkbook register, budget analysis worksheet that I am working on. Right now I am trying to get it to add a total of expenses for a certain month. I would also like it to be able to add just the expenses for a certain month for certain categories.

Sheet 1 (Checkbook)
A1 (DATE) and follows 4/13/11 or dates as I place them in.
B1 (TYPE) (deposit, withdrawal, dc,etc.)
C1 (PAYEE) (Wal-Mart, etc.)
D1 (Category) (has a drop down list to choose items from (gas, grocery, etc))
E1 (Memo) (My note of what it was for)
F1 (Withdrawal) Money that was spent
G1 (Deposit) Money that went in
H1 (Balance) Account Balance

Sheet 2 (Budget/Spending)
A1 (Categories) A2 and so on would be the category list
B1 through M1 and so on would be the 12 months
and column N would serve as complete total.

I need to figure out how to get Budget/Spending B2 to calculate what was spent in Checkbook D1 for only the Month.

I know, I can't explain it. Okay, simply put, I have several items that state Automobile Gas, and it has several dates in a month, I would like Budget/Spending to add up only the Category - Automobile Gas during say the month of January.

Can anybody help? Thank you in advance.

 

[Yard]

Hi and welcome to Mr Excel,

Your data on the Checkbook sheet sounds perfect for a Pivot Table solution!

http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx

 

[rutledgec]

Thanks. I have tried to figure out how to do the pivot table. I still can't figure out how to get it to what I would need to.

 

[Yard]

So, create your Pivot Table with reference to the data range on Sheet1.*

In the Pivot Table, put the Category field into the "Row area"

Put the Date field into the "Column area".

Put the Withdrawal field into the "Values area".

Then you can play around with totals etc.

*You might also want to look at using a Dynamic Named Formula or an Excel 2007-onwards Table to enable your Pivot Table to take into account the expanding set of data on Sheet 1.*

 

[rutledgec]

Thank you again with helping with this. I have tried playing around with getting the pivot table. I have noticed that it will add extra months to my columns, when I just need it to be month by month. It is supposed to grab all data from the checkbook register that only applies to that month, but it is listed in checkbook register as continuous dates of the month (4/13/11). I can't seem to get it to just grab that without making extra months per the dates. Where would be a good place to look at creating the Dynamic Named formula or an Excel 2007-onwards Table. I am bran-spanking new to dealing with pivot tables (this is the first I have tried to create)

Thanks again.

 

[Yard]

OK, for the first issue you can group the Date field by month. Right-click somewhere in the Date field on the Pivot Table and choose Group... then choose Month.

For the dynamic range see here.

For using Tables see here and the blog entries that follow on after it.

 

[rutledgec]

I have a dropdown list that contains atm, dc, etc. When I input something like a check number that is not in my list, it will give me a little green triangle and the little yellow warning, how would I get rid of this annoying little thing so that I won’t have to keep telling it to ignore in all my cells when that happens?
Thanks.

 

[snoopyhr]

see if this get you started :

Excel Workbook
	A	B	C	D	E	F	G	H
1	Sheet 1
2	Opening Balance							1000
3	Date	Type	Payee	Category	Memo	Withdrawal	Deposits	Balance
4	1/1/2011	Deposit					3000	4000
5	2/25/2011	Withdrawal	Check	House Repair		200		3800
6	4/1/2011	Withdrawal	MasterCard	Grocery	Memo 1	100		3700
7	4/2/2011	Deposit					1000	4700
8	4/3/2011	Withdrawal	Visa	Gas	Memo 2	500		4200
9	5/1/2011	Withdrawal	MasterCard	Auto Repair		200		4000
10	6/1/2011	Withdrawal	AMEX	House Repair		100		3900
11	Adjust the range to suit
12	Sheet 2
13	this is your sheet 2 above refers to sheet 1
14		Jan	Feb	Mar	Apr	May	Jun	Jul
15	Gas	0	0	0	500	0	0	0
16	Grocery	0	0	0	100	0	0	0
17	Auto Repair	0	0	0	0	200	0	0
18	Hosehold Items	0	0	0	0	0	0	0
19	Apparel	0	0	0	0	0	0	0
20	House Repair	0	200	0	0	0	100	0
Sheet34

 

[Yard]

I have a dropdown list that contains atm, dc, etc. When I input something like a check number that is not in my list, it will give me a little green triangle and the little yellow warning, how would I get rid of this annoying little thing so that I won’t have to keep telling it to ignore in all my cells when that happens?
Thanks.

This is automatic error checking. If you click the yellow exclamation symbol and choose Error Checking Options, you can customize which "errors" are highlighted or just turn it off altogether.

Is the Pivot Table solution working out for you?