1

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.

1 Answer 1

0

This sounds like a pretty DB to set up.

Start by importing all of your helper tables to an Access database.

Next, import the 12 columns of original data to it's own table.

Create a new query that joins the helper tables to the main table and you will have re-created your dataset.

At this point, I'd suggest making the pivot in Excel and linking it to that query for reporting.

To update the dataset, just import new rows to the main table and refresh the pivot table.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .