Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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" "Martin Los" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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 "Martin Los" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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). regds Charles ______________________ Decision Models FastExcel Version 2 now available. www.DecisionModels.com/FxlV2WhatsNew.htm |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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 -----Original Message----- 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" "Martin Los" wrote in message ... 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel fails me - upgrade to database?
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" wrote in message ... 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 -----Original Message----- 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" "Martin Los" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Web Query fails with "you need to upgrade flash" message | Excel Discussion (Misc queries) | |||
Pivot Table Wizard Fails to Recognize New Rows of Database | Excel Discussion (Misc queries) | |||
Moved database, how do I change link to the database in excel | Excel Discussion (Misc queries) | |||
Workbooks.Open fails after upgrade to XL2000 from XL97 | Excel Programming |