If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A bit confused on my budget spreadsheet.
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? |
#2
|
|||
|
|||
A bit confused on my budget spreadsheet.
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. -- Regards Roger Govier "LiveUser" wrote in message ... 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? |
#3
|
|||
|
|||
A bit confused on my budget spreadsheet.
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" wrote: 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. -- Regards Roger Govier "LiveUser" wrote in message ... 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? |
#4
|
|||
|
|||
A bit confused on my budget spreadsheet.
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. InsertnameDefineName myData Refers to =$A$1:INDEX($D:$,COUNTA($A:$A)) In the PT when asked for Source, enter =myData -- Regards Roger Govier "LiveUser" wrote in message ... 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" wrote: 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. -- Regards Roger Govier "LiveUser" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|