I have built an Excel-based reporting system which seems to get out of shape. I am looking for alternative solutions in order to overcome Excels limitations.
My reporting solution aggregates time bookings. I run these reports on a monthly base, exporting about 5000 booking events per month from the time reporting system. I paste these into an excel sheet which complements each booking event with e.g. project information, organization information and so on. The excel sheet contains 12 columns with original imported input data and 15 columns looking up data based on the original data (via vlookup from helper tables).
This source sheet (5000 rows x 27 columns) is then aggregated via pivot tables.
Right now, I create a new Excel workbook per month, in order to keep this managable with Excel. Editing or filtering data sometimes takes about 10 seconds to re-calculate and the sheet seems to get more and more instable, putting Excel to 100% CPU load, to be killed via task manager.
I am looking for a replacement, which can:
- hold at least a year of data (60.000 rows)
- do the same comfortable ad-hoc pivot analysis as Excel does
- automates the lookup in the source table so I get rid of copy/paste of formulas
What would you recommend as an replacement?
- Move the data store to Access (I have Access 2003 available) and leave the pivots in Excel?
- Put both, data store and pivots, into Access?
- Move the data store to some "real" SQL database, keep the pivots in Excel?
- Something even more different?
This is a single-user desktop application.
I am on Excel 2003.