To Excel or not to Excel

[Goldenballs]

I have to set up an 'application' to measure the performance of approx. 70 sales advisors. Each adviser has 10 of their calls monitored per month, and are marked on 30 different questions per call.

The application must be able to calculate scores per adviser, but also be able to drill down to see scores for each team and the department as a whole.

I've tried setting something up in Excel, which looks good - but one workbook is over 22.M, and I need eleven workbooks!! My companies shared drive simply cannot cope.

Can anyone advise whether I should ditch Excel? Will Visual Basic be able to cope or should I switch to Access?

Help!!!

 

[dk]

Hi,

22 Mb seems very large for an Excel workbook. Workbooks of that size are normally destined for corruption, and the loss of your data.

My personal opinion is that any data amounting to this volume should be stored in a database - which is designed for this purpose. You can then query that database and analyse the results in Excel in the way of a pivot table or query, without making Excel do something it's not designed for.

If you have Access available then I'd recommend you use it. Make sure that your database is well designed with your data organised efficiently (try to reduce data duplication whilst not making your database too involved).

Good luck to you mate. If you have any questions then don't hesitate to post either here or in the Access forum of this site.

 

[Mark W.]

If your PivotTables are sharing the same source data you can reduce your workbook size by sourcing all your PivotTables from a single PivotTable. Select each of your PivotTables (in turn) and choose the PivotTable Wizard. [< Back ] to Step 1 of 3 and select the "Another PivotTable report..." option.