Trimming down a file
 

I have a file that a co-worker that is 20mb with out entering any data. He
used indirect for alot of his formulas. one of the tabs ranges from A8 to
AF348, almost every cell has a indirect formula in it.
an example of one of these fomulas is below (this is in almost every cell)
with some variation
=IF(INDIRECT(ADDRESS(D$2+3,12,,,"SymTypes"))="","" ,IF(INDIRECT(ADDRESS($AJ$16+($A332-1),$AK$16,,,$AL$16))=INDIRECT(ADDRESS(D$2+3,12,,," SymTypes")),1,""))
Is there a way to make this smaller by using a different formula or using
VB. I like this file but it is just to big I have one that got as big as 56mb
Any feedback will be welcome

Trimming down a file
 

Using lots of INDIRECT won't impact the size much...
it might impact the performance.

Just to test
Select the whole sheet
Copy
Paste Special|Values

Save to another file
Compare sizes...

Let us know the results...

I paste your formula as a string to the range A1:AF1000 and the size came to
about 500KB.

Are there many PIVOT tables? Any images? Excessive formatting?

"Novice Lee" wrote:

I have a file that a co-worker that is 20mb with out entering any data. He
used indirect for alot of his formulas. one of the tabs ranges from A8 to
AF348, almost every cell has a indirect formula in it.
an example of one of these fomulas is below (this is in almost every cell)
with some variation
=IF(INDIRECT(ADDRESS(D$2+3,12,,,"SymTypes"))="","" ,IF(INDIRECT(ADDRESS($AJ$16+($A332-1),$AK$16,,,$AL$16))=INDIRECT(ADDRESS(D$2+3,12,,," SymTypes")),1,""))
Is there a way to make this smaller by using a different formula or using
VB. I like this file but it is just to big I have one that got as big as 56mb
Any feedback will be welcome

Trimming down a file
 

I did what you suggested on two of the tabs that have the most formulas in it
it went from 21mb to 8mb

I was wondering if I could break the two tabs into 2 seperate files can
indirect look in another file?


"Sheeloo" wrote:

Using lots of INDIRECT won't impact the size much...
it might impact the performance.

Just to test
Select the whole sheet
Copy
Paste Special|Values

Save to another file
Compare sizes...

Let us know the results...

I paste your formula as a string to the range A1:AF1000 and the size came to
about 500KB.

Are there many PIVOT tables? Any images? Excessive formatting?

"Novice Lee" wrote:

I have a file that a co-worker that is 20mb with out entering any data. He
used indirect for alot of his formulas. one of the tabs ranges from A8 to
AF348, almost every cell has a indirect formula in it.
an example of one of these fomulas is below (this is in almost every cell)
with some variation
=IF(INDIRECT(ADDRESS(D$2+3,12,,,"SymTypes"))="","" ,IF(INDIRECT(ADDRESS($AJ$16+($A332-1),$AK$16,,,$AL$16))=INDIRECT(ADDRESS(D$2+3,12,,," SymTypes")),1,""))
Is there a way to make this smaller by using a different formula or using
VB. I like this file but it is just to big I have one that got as big as 56mb
Any feedback will be welcome

Trimming down a file
 

It will look in whatever cell the value within it evaluates too.

I am surprised it came down.

Can you upload the file at wikisend.com and mail me the link?

"Novice Lee" wrote:

I did what you suggested on two of the tabs that have the most formulas in it
it went from 21mb to 8mb

I was wondering if I could break the two tabs into 2 seperate files can
indirect look in another file?


"Sheeloo" wrote:

Using lots of INDIRECT won't impact the size much...
it might impact the performance.

Just to test
Select the whole sheet
Copy
Paste Special|Values

Save to another file
Compare sizes...

Let us know the results...

I paste your formula as a string to the range A1:AF1000 and the size came to
about 500KB.

Are there many PIVOT tables? Any images? Excessive formatting?

"Novice Lee" wrote:

I have a file that a co-worker that is 20mb with out entering any data. He
used indirect for alot of his formulas. one of the tabs ranges from A8 to
AF348, almost every cell has a indirect formula in it.
an example of one of these fomulas is below (this is in almost every cell)
with some variation
=IF(INDIRECT(ADDRESS(D$2+3,12,,,"SymTypes"))="","" ,IF(INDIRECT(ADDRESS($AJ$16+($A332-1),$AK$16,,,$AL$16))=INDIRECT(ADDRESS(D$2+3,12,,," SymTypes")),1,""))
Is there a way to make this smaller by using a different formula or using
VB. I like this file but it is just to big I have one that got as big as 56mb
Any feedback will be welcome