Excel fails me - upgrade to database?

  • Thread starter Thread starter Martin Los
  • Start date Start date
M

Martin Los

At work we analyze salesdate of several departments for
years 2002, 2003 and 2004. In total I have a worksheet of
30 MB (!), 10 worksheets (some of which have 20.000 lines).

From the worksheets we get the date using vlookup and
sum.if functions to get a summary report with comboboxes,
allowing the user to select summary of days or months.

Due to enormous amount of functions to calculate, the
worksheet isn´t practical anymore; it takes ages (> 10
seconds) for each query of the combobox to be executed. On
my P-III 600Mhz (with 256 Mhz internal memory) with
Windows and Office XP I can´t even get workbooks opened
anymore!

I am looking for a solution:

1. Get a more powerful computer
(Disadvantage: my collegeaus with P-IV 2,4 Ghz and 256 Mhz
have same problems - so this solution does not seem
adecuate)

2. Get data into a real database.
(Disadvantage: the formula´s used can´t be exported easily)

Does anybody have suggestions as how to get our workbooks
manageable?

TIA

Martin
 
Without seeing the whole thing I can only make some general comments.

As i see it you have two choices: spend your time transferring this
application to a database, or spend your time nursing this ailing
application along.

A bigger box might work in the short term, but if you are adding data and
functionality you will, probably, soon be back in the position you are in
today.

Complicated formulas? Databases work in a different way - you might not need
complicated formulas (databases are usually very strong on reporting).
Or, keep your data in a database and extract subsets of data you want to
manipulate to Excel.

HTH
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



At work we analyze salesdate of several departments for
years 2002, 2003 and 2004. In total I have a worksheet of
30 MB (!), 10 worksheets (some of which have 20.000 lines).

From the worksheets we get the date using vlookup and
sum.if functions to get a summary report with comboboxes,
allowing the user to select summary of days or months.

Due to enormous amount of functions to calculate, the
worksheet isn´t practical anymore; it takes ages (> 10
seconds) for each query of the combobox to be executed. On
my P-III 600Mhz (with 256 Mhz internal memory) with
Windows and Office XP I can´t even get workbooks opened
anymore!

I am looking for a solution:

1. Get a more powerful computer
(Disadvantage: my collegeaus with P-IV 2,4 Ghz and 256 Mhz
have same problems - so this solution does not seem
adecuate)

2. Get data into a real database.
(Disadvantage: the formula´s used can´t be exported easily)

Does anybody have suggestions as how to get our workbooks
manageable?

TIA

Martin
 
Put your data in Access (all the data in one table with years as one of hte
fields - or whatever would put the data on separate sheets included as part
of each record identity.

Link to the data base from Excel using a pivot table (data=>Pivot Table
Report).

--
Regards,
Tom Ogilvy


At work we analyze salesdate of several departments for
years 2002, 2003 and 2004. In total I have a worksheet of
30 MB (!), 10 worksheets (some of which have 20.000 lines).

From the worksheets we get the date using vlookup and
sum.if functions to get a summary report with comboboxes,
allowing the user to select summary of days or months.

Due to enormous amount of functions to calculate, the
worksheet isn´t practical anymore; it takes ages (> 10
seconds) for each query of the combobox to be executed. On
my P-III 600Mhz (with 256 Mhz internal memory) with
Windows and Office XP I can´t even get workbooks opened
anymore!

I am looking for a solution:

1. Get a more powerful computer
(Disadvantage: my collegeaus with P-IV 2,4 Ghz and 256 Mhz
have same problems - so this solution does not seem
adecuate)

2. Get data into a real database.
(Disadvantage: the formula´s used can´t be exported easily)

Does anybody have suggestions as how to get our workbooks
manageable?

TIA

Martin
 
If removing the formulae gets your workbook to a manageable size you might
consider an intermediate step of keeping your data in Excel and just using
pivot Tables for your reports instead of sumifs and lookups.

This assumes that your data will not grow too large (how many years do you
want to hold?).
Put your data in Access (all the data in one table with years as one of hte
fields - or whatever would put the data on separate sheets included as part
of each record identity.

Link to the data base from Excel using a pivot table (data=>Pivot Table
Report).
Click to expand...


regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Most probably we will be going to implement a database
solution. Since we cannot design our own Access database
(central company IT regulations forbid workers to design
own databases since without central knowlegde/control no
support can be given in case of problems), we will have
the IT department to design (probably with Discovery DB)
our new application.

Any suggestions as how to go along with the design?
- What aspects of database are important;
- What costs could be involved for a 10 persons access (I
ask since we never have had such a project).

TIA again for suggestions!

Martin
 
If IT have to design and implement the new application then I suggest you
let them guide you.

Sounds as though you will be going through a full development life-cycle:
* Feasibility study
* System analysis
* System evaluation and design
* Coding (prototyping and programming)
* Testing and user feedback
* Implementation

Costs for later stages will depend on what is found in the feasibility study
and in the system analysis. You might find that the analyst spots ways that
can make significant improvements to your current system without the need
for a database.

To me, the most important aspect of database is design is getting the data
relationships correct (you might hear the analyst call it "normalisation").
This means asking users a lot of questions about the data, where it comes
from, how they currently use it and how they intend to use it. Outputs, such
as results and reports, should flow natually from this analysis.

On one other issue you raise, ".. since without central knowlegde/control no
support can be given in case of problems ..". There's no difference here
between spreadsheets and databases. Who really knows what your current
spreadsheet application does? In my experience these things have passed
between several people, each of whom has made their only contribution to the
overall structure and functionality. When I go into a company (usually to
take over a rogue Excel project) I ask the traditional question, "Do you
have any documentation?", to which I've usually receive the tradional reply,
"No".

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



Most probably we will be going to implement a database
solution. Since we cannot design our own Access database
(central company IT regulations forbid workers to design
own databases since without central knowlegde/control no
support can be given in case of problems), we will have
the IT department to design (probably with Discovery DB)
our new application.

Any suggestions as how to go along with the design?
- What aspects of database are important;
- What costs could be involved for a 10 persons access (I
ask since we never have had such a project).

TIA again for suggestions!

Martin
 
Back
Top