How can I reduce long calculation times on large sheets?

[claytorm]

Hi,

I have a sheet which brings in about 5000 pieces of data from anothe
sheet using a match formula. 5000 similar simple calculations are the
performed on this data. The problem is, even if I make a small change
it recalculates all the data, including all the match formulae. Thi
takes around 5 mins on a decent machine. I have turned of
auto-calculate, but re-calculation happens every time I save. Is ther
any way I can lock some of the data? Any suggestions welcome.

Along the same lines, what are the best options when a sheet fills up?
I have huge numbers of share prices in a sheet, and this generates
file about 6MB, which, when it loads, pops up with a note saying tha
it has not fully loaded. Do I need to think about putting this data i
some sort of database, or is there a way I can expand the max size of
sheet.

Many thanks,

Berti

 

[Don Guillett]

If your formulas don't change often consider using a macro to create the
formula and then change to values or just have the macro create the values
without creating the formula first. This gets rid of the calculation
overload. Simple example
Sub balance()
set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
With frng
.Formula = "=h7+d8"
.Formula = .Value
End With
End Sub