Excel Reporting...

I have a small group of people in collections who spend their days creating and parsing out excel spreadsheets containing invoice status information. Customer, invoice, file number, amount, status (to be paid, processing, not received by customer, etc...), and various other bits of invormation. Creating these isn't so bad - essentially matching several data sources in Excel by invoice number with a V lookup or index formula.

However, parsing them out is a MAJOR time sink: Number of customers multiplied by the number of business units, local managers, and area controllers involved in each account.... And all of these people want slightly different views of their worlds.

I am looking for a tool which can use to design a report(s), then use a large~ish Excel Spreadsheet (a few hundred up to 15 thousand lines by 20~30 columns) to automatically create those reports for the different internal consumers. A near-perfect solution would also be able to use Outlook to automatically e-mail them out to pre-defined recipients with some kind of standard message: So the Area Head gets a message saying "Dear Sir, attached please find the bi-weekly...", with an attachment containing his report. While the guy in Operations gets "Yo Jackass, you didn't do your billing this month.... Again....", and his attachment tells him what he needs to get done. This would save DAYS of time.

Is anyone familiar with a product or tool like that?? I've been googling, but many/most of the products I've found so far seem to be a lot heavier duty than we actually need.

I'm not that great with it, but from what I've seen, MS Access promises to do something like that. I'm not sure to the extent or anything like that, but the capability does exist. I have only started playing with Access; since I have no actual use for it, I'm mostly going through tutorials with no end in mind, so it isn't clicking for me that fast.

Another option would be to jump into the Visual Basic part of Excel and write code for it to do what you're talking about. I can tell you, though, that this is a bit time consuming for a non-dedicated coder. If you have someone like that in your office who has the job of writing support code, it may be worthwhile to give them as a job.

Maybe someone else will have something for you. Still, I would expect that Microsoft would include some capability like that in the Enterprise or Business edition of Excel. If you have Office 2007, it would be worth your while to look at Access as an option. Additionally, MS Groove, InfoPath, and OneNote may help streamline some operations.

Good luck.

MS Access is definately the way to go.