| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| TRIM not trimming | Excel Worksheet Functions | |||
| TRIM not trimming | Excel Worksheet Functions | |||
| Trimming text | Excel Worksheet Functions | |||
| Trimming Data | Excel Worksheet Functions | |||
| IP Trimming | Excel Discussion (Misc queries) | |||
Linear Mode
