Im sorry, I got a little twisted up, 50-60 thousand Rows, an average of 40-60
columns and probably an average of 15ish worksheet per file. It is still
ALOT of data. There are multiple lookups and pivot tables that tie 10-12 of
these files (many of which this size) together, he realistically has 30-35gb
of data in Excel across 90-95 files, it's simply horrendous. Hats off to
him, he has done some brilliant work for us in Excel, but he has pushed Excel
WAY too hard. I have written some pretty heavy duty excel lookups and
formulas, but his make me shudder. Frequently his "basic" lookups and
formulas are 3-5 lines in the formula bar, his heavy ones are WAY bigger.
There are some Macros written to "simplify" thi, when one of them (not the
biggest one by far) was printed for trouble shooting, it was 17 or 18 PAGES
of macro script, for about 10 of these files, that were all the horrid size I
was mentioning. Several files over 100meg in straight Excel.

Is it a safe assumption that is you are at the 100mg area of an excel file,
you should probably be moving forward to something else just from that alone.
Sorry for the confusion, please, a second opinion if you would? :)

"JLatham" wrote:

If the numbers you gave are even close to right, it sounds like the time has
probably passed to move to something more robust. But to say that that
something else is Access would actually require an analysis of the data on
the sheets themselves. If many (most) of the sheets could be grouped into
similar categories with similar information/formulas/processes within those
groups of sheets, then I'd say "yes, it's time to move this to a relational
database that's more appropriate for not only the volume but also more
appropriate to handling repeating data groups".

You start talking the numbers you did with regards to Excel in both terms of
# of worksheets and size of the files, then I start to cringe.

"dlw" wrote:

Did you say 50 to 60 THOUSAND sheets? And your head of PLANNING is doing all
that? You should change his title to Head of Spreadsheet Empire. But with
something like that, the word Empire seems inadequate.

"Dave B." wrote:

I think we have pushed Excel too far. There is an ongoing issue with excel
being rather slow, but it is only slow for certain people on certain files.
The IT dept and Planning have been head butting about this for some time, and
I am wanting some 3rd party perspective. Our head of planning has been
writing increasingly large excel sheets. Several in the 80-120mb area, with
multiple worksheets numbering in the 50-60 thoughsand + area, with an average
of 10-14 fomulae per worksheet, and each formula is extended the entire
heighth of the worksheet and the formulae themselfs are quite sizeable. This
is combined with many large pivot tables and complicated lookups and offset
lookups going to other files of comparable size and complexity. My question
is this, should this be migrated over to access? I figure it should be since
Access is designed for this kind of data base volume, and as far as I can
tell Excel seems to have reached and passed what it can process effeciently.
He frequelty have upwards of a 1/2 gig open in excel files at once, from 4-6
files, and when he is on and working, everyone else on that terminal (Citrix)
server is feelig the strain (and said server is new and top of the line) and
everyone in that yard sharing the T1 is also feeling the effects.
What do you guys think?