Excel file size restrictions/problems

[Sam_Gregson]

Hi guys - hope you can help.

I recently produced a toolkit for a fuel cell company using Excel 2007.

The toolkit undertakes calculations on domestic energy demand data sets which record values for every 5 minute period (e.g. heat demand, electricity demand) of the whole year. Therefore the toolkit at present is extremely large - simply because it stores thousands of rows of formulae.

(N.B - I am sure i could have done things in a far more expedient way but the client likes the current format)

My question relates to errors the client is now receiving from the toolkit following handover.

He is getting errors such as "Excel cannot complete this task with the given resources" when he tries to open and operate the toolkit. However, when he looks at CPU useage there is still plenty of RAM/page file space available.

I wanted to ask whether anyone knows if Excel 2007 actually has its own file size/computational speed limits independent of the performance of the wider computer - this could help explain why the client is getting errors even on a very powerful and fast machine.


Hope you can help and advise.

Thanks,

Sam

[broro183]

hi Sam

Here's a link to a useful page on Charle's Williams' site regarding the limits of Excel 2007: http://decisionmodels.com/memlimitsc.htm
(he has a lot of useful info across his site)

...Therefore the toolkit at present is extremely large - simply because it stores thousands of rows of formulae.
(N.B - I am sure i could have done things in a far more expedient way but the client likes the current format)...

I suggest that you do redesign the file in a more expedient way (possibly by refining the formulae, using pivot tables or even using macros?) & justify this approach to the client on the basis of ongoing usability.

If you upload an example workbook with the layout, just a few rows of formulae & dummy data, someone may be able to suggest ways of optimising your file.


hth
Rob

[Sam_Gregson]

Thank very much - i'll have a look at that

I may also upload a dummy file later so that people can discuss the best way to improve the operation of the toolkit.

I would be happy to use macros to improve the useability.

Thanks again,

Sam