A bit confused on my budget spreadsheet.

  • Thread starter Thread starter LiveUser
  • Start date Start date
L

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?
 
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.
 
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?
 
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
 
Back
Top