 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Setup / February 2008A 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? 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.  SignatureRegards Roger Govier > Expense 1 Expense 2 Expense 3 Expense 4 > 1-Jan-08 [quoted text clipped - 30 lines] > > Is there a simple way to do this, or a better way? 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 > [quoted text clipped - 58 lines] > > > > Is there a simple way to do this, or a better way? 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 SignatureRegards Roger Govier > Roger, > [quoted text clipped - 74 lines] >> > >> > Is there a simple way to do this, or a better way?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.