Is Excel the wrong tool to use in this case?

[OldDaddy]

1. In Excel I have 1 summary sheet that takes 2.5 – 3 minutes to recalculate. 2. It recalculates whenever I make any small change – unless I turn the Calculation option to Manual. 3. There are 10 other sheets in separate workbooks that feed different tabs in the summary workbook and then into the one summary sheet (some are .xlsx and some .csv). 4. I download all data into the various sheets from our major accounting/manufacturing application because that application does not provide for adhoc reporting. Most of these "other" sheets contain between 4,000 and 10,000 rows and 10 columns, 6 are transaction files (only 1 major), 2 are master files providing static descriptions, conversion factors, etc, 1 is opening/closing stock file. Am I over-extending the use of Excel and should be using MS Access or MySQL or some other database application. Anybody have any thoughts on this?

 

[Fazza]

Welcome,

On first reading, it does sound like time for a database. In fact it sounds like an ideal task for a database - interacting with your major accounting/manufacturing application, storing static data & easily making reports. However, perhaps Excel can handle what you want with a different approach. Such as working more like a database and using pivot table and or data queries to replace formulas. Depending on what you're doing, maybe all the worksheet formulas can be replaced? These are general comments, of course: for specific advice further understanding of the data & requirements would be needed. Though just because one could do this in Excel (with some tricks/smarts) doesn't mean it is the way to proceed.

If future requirements will increase the volume of data, it might be a great time to start on a database solution.

OK?

 

[OldDaddy]

1. In Excel I have 1 summary sheet that takes 2.5 – 3 minutes to recalculate. 2. It recalculates whenever I make any small change – unless I turn the Calculation option to Manual. 3. There are 10 other sheets in separate workbooks that feed different tabs in the summary workbook and then into the one summary sheet (some are .xlsx and some .csv). 4. I download all data into the various sheets from our major accounting/manufacturing application because that application does not provide for adhoc reporting. Most of these "other" sheets contain between 4,000 and 10,000 rows and 10 columns, 6 are transaction files (only 1 major), 2 are master files providing static descriptions, conversion factors, etc, 1 is opening/closing stock file. Am I over-extending the use of Excel and should be using MS Access or MySQL or some other database application. Anybody have any thoughts on this?

Thank you for your reply Fazza, that is pretty much along the lines that I was thinking. I will investigate the database options further and also look at using data queries to access the data direct from our accounting/manufacturing application.

 

[Fazza]

You're welcome. regards