Question re best practice
 

hello XLers

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to me
that this long formula is repeated 4800 times, making the file kind of big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells with
values calculated in code, rather than write the formulas to the cells? Are
there speed issues?

Any comments are appreciated
anny

Question re best practice
 

Surely, unless you have a very small HDD, the size should not be an issue.
Performance may well be, but you do not mention that. I certainly wouldn't
do what you suggest juts to make the saved file size smaller.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"anny" wrote in message
...
hello XLers

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to
me
that this long formula is repeated 4800 times, making the file kind of
big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells
with
values calculated in code, rather than write the formulas to the cells?
Are
there speed issues?

Any comments are appreciated
anny

Question re best practice
 

I dunno about "best", but I have one book with 38 sheets and about 1200
VLOOKUP's on each sheet, and I did as you describe, delete the formulas when
the sheet is deactivated and put them in place with a macro when the sheet
is "updated" (not just opened, as some editing of ths sheet is desirous
without the formulas being activated).....seems to help the recalc time....

Vaya con Dios,
Chuck, CABGx3


"anny" wrote in message
...
hello XLers

I have a general question about best practice in Excel . My workbook has
about 40 sheets. On each sheet, there is a very lengthy formula dragged
over a range of about 120 cells. Everything works fine, but it occurs to
me
that this long formula is repeated 4800 times, making the file kind of
big.

Is it better practice to use code to populate the cells with formulas, say
when a sheet is activated? Should I then depopulate the cell's formulas
when the sheet closes? Is it better to use code to populate the cells
with
values calculated in code, rather than write the formulas to the cells?
Are
there speed issues?

Any comments are appreciated
anny