Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
Im sorry, I got a little twisted when I wrote that. 50-60 thousand ROWs, and
an average of 50-70 columns, and probably 15ish (average, but as high as 45 on some) worksheets per file. Usually 5-10 files like this opened at once, all tied together via pivot tables and various types of LARGE lookups (and by large, I mean bare minimum of 3 lines of text, usually more like 10-12). Also he has various marcos that are tying usually 10-12 files together as well, and the print out of his average macros, printed for trouble shooting purposes, was 17 or 18 pages, some other are ALOT bigger. I personally have writen some fairly heavy excel stuff, but what he has makes me shudder. He is realistically sitting on upwards of 35gb of raw excel data spread across 75-80 files. "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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
Please only read the second reply,sorry to double post...... it showed it
didnt post the first reply I made. "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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Is it time to move to access?
double post - no trouble, sometimes the system does that to us all.
As for the 100MB limit you've 'set' - again it depends on where that 100MB is coming from. If these files contain unrelated data on lots of sheets, then it may not be realistic. But he may want to move those files to his local hard drive with regular backups made to the server/share. He'll get better performance, especially with his linked data, and the network load will be greatly reduced. That in turn lets other people get on with their own lives better and be more productive to the company as a whole. My gut feeling of the whole thing is that he probably would be better served using a database of some type. Access might handle the load since he's probably the only user, although something a bit more robust as perhaps SQL Server if you have it around, or the free SQL Server Express Edition ( http://www.microsoft.com/sql/edition...s/default.mspx ) with Access as the front end for it (for easy interface development). "Dave B." wrote: Please only read the second reply,sorry to double post...... it showed it didnt post the first reply I made. "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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort tabs in a workbook other than one at a time with move or cop. | Excel Worksheet Functions | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
How do I move or copy more then one sheet at a time | Excel Worksheet Functions | |||
Move to Access? | Excel Worksheet Functions | |||
sort tabs in a workbook other than one at a time with move or cop. | Excel Worksheet Functions |