A bit confused on my budget spreadsheet.

[LiveUser]

Expense 1 Expense 2 Expense 3 Expense 4
1-Jan-08
2-Jan-08
3-Jan-08
4-Jan-08
5-Jan-08
6-Jan-08


I have the entire year (by day) in column B. Row 2 is going to be expenses.
Simply, I want a calendar that I can enter daily expenses into and have it
update a master summary.
I am confused because I don't know what I should do or what is the easiest
and most logical way to do this.
On the master summary I have categories, like gas, groceries, etc. I want to
be able to enter a dollar amount into the day, but also have a tag, like gas,
that the master spreadsheet will grab the dollar amount information and add
it to gas, same with groceries, etc.
How do I make January 1, 2008 have one line until something is entered?
Let's say on my spreadsheet January 1, 2008 I bought gas, groceries, went to
a movie, bought a key ring, and bought more groceries.
I would like the categories to be something like (rather than Expense 1, 2,
etc.):
Auto Groceries Entertainment Misc.

But, under each category, for instance, Auto, there would be gas, care
maintenance, etc.

Is there a simple way to do this, or a better way?

 

[Roger Govier]

Hi

This is the wrong way to go.
Set up a sheet with the following headings in row 1
A1 B1 C1 D1
Date Category Sub-Category Amount

From Row2 onward, use as many lines per day as you have transactions

01/01/08 Auto repairs 65.00
01/01/08 Gas 20.00
01/01/08 Groceries 35.50
02/01/08 Entertainment Movie 15.00

You will not have transactions for every day of the year, and some days you
will have a single transaction, others there will be multiple.
The whole set of data can then be summarised with a Pivot table to give you
an analysis by Category (and Subcategory if required) by month.

For more help on Pivot Tables take a look at
http://www.datapigtechnologies.com/ExcelMain.htm
and
http://www.contextures.com/tiptech.html scroll to the section on Pivot
tables
Whilst at Debra Dalgleish's site, also take a look at the Data Validation
section, as that will show you how you can create dropdown lists to select
your categories as you are entering data on your main sheet.

 

[LiveUser]

Roger,

Thank you for the information. That does seem like a better idea, but what
if I buy groceries twice in one day and don't want to put the amount in the
same cell? Is this something easy I am over looking?

 

[Roger Govier]

Hi
Then use another row.
Just think of it as each transaction uses a separate row. It doesn't matter
how many you have in a file, and I doubt you will get anywhere close to
Excel 2003 (and lower) limit of 65536 rows.

With your source data it would be better to create a dynamic range that will
grow as you add more rows, rather than having to keep redefining the range
in the PT.
Insert>name>Define>Name myData >Refers to
=$A$1:INDEX($D:$,COUNTA($A:$A))

In the PT when asked for Source, enter =myData