7500 formulas too much?

[aiwnjoo]

1500 VLOOKUPS
4500 SUMS
1500 AVERAGE

Hi, currently i have a single sheet within my workbook that has the following, please also see attachment, this is just just 1 month of data collection.

I am using Excel 2000 on a networked machine, when this spreadsheet is open it causes a massive bottleneck due to the amount of formulas in use, i have attached the sheet for you to look at and hopefully there is some sort of workaround.

[Bob Phillips]

Hard to say, it refers to other workbooks that we don't have.

[aiwnjoo]

Well i cant zip up 52 workbooks, and you would also then need to emulate the same path, surely ans answer can be suggested without that?

[canapone]

Hi, probably you're having problems with the so called heap space : too many external links with long addresses to keep in memory could create the slowing down in calculations.

The following link is very interesting:

http://www.decisionmodels.com/memlimitsc.htm

Regards

[aiwnjoo]

Gonna try and reduce the formula size or ranges atleast in someway.

[aiwnjoo]

Do you think that 1500 INDEX/MATCH would be better than 1500 VLOOKUPS?

[Bob Phillips]

Maybe, but only marginally.

SOrted data helps, as would be using dynamic ranges.