With this complicated of a job, you need to use the right tools for the job. Excel is good software, but Access is specifically designed for exactly this type of project.
To use pivot tables in Excel 2007:
Highlight the data you wish to turn into the table. Click on the insert tab on the ribbon (2nd from the left) and choose pivot table on the far left. Click on OK and drag the fields you want to sort into the appropriate boxes.
For example -
I use a pivot table to sort my credit card charges into jobs/gl categories. My spreadsheet looks like this:
Date Description Debit G/L Job
6/29/2011 MCGUCKIN HARDWARE 4.85 Ofc Supply TVOG
7/1/2011 USPS 07088395520304440 15.37 Postage PIC
7/7/2011 USPS 07088301330301535 54.45 Postage SAW
7/11/2011 SAFEWAY STORE 00016154 7.74 Ofc food TVOG
7/12/2011 DOLRTREE 2946 00029462 10.29 Ofc Supply TVOG
7/20/2011 USPS 07088301330301535 10.95 Postage PIC
7/28/2011 OFFICE MAX 128.33 Ofc Supply TVOG
7/28/2011 STAPLES 00114157 104.93 Ofc Supply TVOG
I select the debit, g/l, and job columns and click on insert-pivot table. Next I drag Job THEN g/l into the row labels box. Next I drag debit into the values box. The final pivot table shows as:
Row Labels Sum of Debit
PIC 26.32
Postage 26.32
SAW 54.45
Postage 54.45
TVOG 256.14
Ofc food 7.74
Ofc Supply 248.4
Grand Total 336.91
PM me if you want would like me to email you the jpeg of the spreadsheet with the completed pivot table (since they don't line up all pretty here
)). Hope this helps!
Database Suggestions
Lily Chambers, The Virtual Office Goddess, LLC
Linear Mode
