sweetloop

I’m making a spreadsheet to give to people to help keep track of personal finances. I know Excel fairly well, but can’t figure this one out. I type in a record of each expense with the date, type (Cash, visa, cheque, etc.), description and the amount. I also want to show all this data again further down on the page only sorted by type (i.e. all the visa expenses in one section, all the cash expenses in another, etc.). Right now I’m typing everything in twice, but this takes quite a while if there are a lot of expenses that month. Does anyone know how I could do this automatically? It's a bit hard to describe this so I’m attaching an example to illustrate (Yes I know it's ugly, it's just to show what I'm trying to do). I can post again if any clarification is needed. Thanks in advance for your help.

Attached Files

Example.rar (2.8 KB, 8 views)

Anne Troy

If you think something like the attached would be more appropriate (and being an Excel geek, I have to say you'll be far happier with maintaining it), then you can record macros and assign buttons to run them. For the workbook to work for you, you MUST enable macros. If you like it, go on over to www.vbaexpress.com and register on their forum and ask some of the programmers to help you out. They're very helpful if you beg nicely. I do it all the time. I just record macros. You'll need a little more than that so that it's done right. Also, I made the macros run using buttons on the worksheet. It's just as easy to put buttons on your very own toolbar. You could have one button for each grouping. And clicking it could even show you a total for each grouping you're picking, etc.... very cool.

Attached Files

Book1.zip (10.1 KB, 5 views)

sweetloop

Thanks Anne. Although this isn't exactly what I'm looking for, it is helpful. Unfortunately, doing it this way will lose the classification by expense type (Food, misc, travel).

An additional detail that I didn't include is that in the spreadsheet is meant to be static and not interactive so that it can be easily printed. Because of this it is necessary to have the different types of expenses in the different boxes and a button/drop-down list to filter is out. I'm hoping that I'll be able to do this without using macros and/or VBA code so that the people I give this to won't have to mess with their macro settings.

Can anyone point me to a way that this could be done using existing Excel functions? I think it would be possible using something like LOOKUP, MATCH, INDEX, etc. but can't figure it out. I'll try some more this weekend. Any help or advice is very much appreciated.

Anne Troy

What is the maximum total number of entries you believe you'd have in a week/month/year (whichever period you want your workBOOK to cover)?

sweetloop

The workbook tracks monthly expenses. It is still a work in progress, but at the moment I've 15 lines per expense category (e.g. food, travel, misc, etc.) and 40 lines per payment type (cash, visa, cheque, etc.). So far this seems to be enough but I will expand it if need be.

dheerajnagpal

Hi,

In case you just need to escape the effort of typing multiple times, you can create one consolidated sheet and use pivot tables to show data as desired.

I am attaching the sheet of yours with your data.
You can create multiple pivot tables on one sheet and can then use them for printouts.

Hope this helps.

Cheers
Dheeraj

Attached Files

Example.zip (2.9 KB, 4 views)

sweetloop

dheerajnagpal,

Thank you, that's just what I'm looking for. I think I'll hide the data table or put it on a separate tab then report on the data in the report. Thanks for your help!