Results 1 to 10 of 10

20040902, 12:34 #1
 Join Date
 Aug 2004
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
ways to reduce file links memory (office XP)
hi, there are 4 Excel files links together to support data. one has 8k KB, one has 29k KB, one has 77k KB and last one has 38k KB. these files have data, Excel function formulas, pivot charts, pivot tables and their memory will grow as new more data coming in. can anyone suggest anyway to reduce the files burden to recalc? THANKS.

20040902, 15:01 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: ways to reduce file links memory (office XP)
here is some info on reducing the size due to pivots.
reducing formulas is not so general. Some suggestions: get rid of array formulas they calculate slowly since they have many calculations in each one. Many times a macro can do it much, much faster and use alot less memory.
IFs are memory hogs.
builtin (nonarray) functions (even megaformulas) are faster than userdefined functions
if you use the same formula a lot (just copied), a "named formula" might offer some memory improvement.
If your data does not change alot, copy  paste special values can be done (saving perhaps 1 row of calcs) and then copying that row as needed to update and then changing to values again.
Steve

20040910, 00:07 #3
 Join Date
 Aug 2004
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
hi Steve, THANKS a lot for your many suggestions on reducing file memory. my 4 files now can improve its memory to about 77k KB and i shrink them into two files now and that is a lot for me, around 80% of my data are merely copied down calculations from row 10 until row 4461 and increasing rows of new data everyday. now i use the copypaste speacial values for above formulas and leave a number of rows below with formulas to update necessary new data to generate new result. around 70% of the copied formulas calculations use IF function, others use a mixture of formulas, some pivot charts and pivot tables. i think maybe the named formula comment can be use somewhere i just have to pick up the theory and practice. what do you mean by IFs are 'memory hogs'? are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn't it? i don't understand the ' builtin (nonarray) functions (even megaformulas) are faster than userdefined functions ' but i think builtin functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba i will check more later. all my formulas obtained from the excel function list. and i haven't ponder upon pivot memory improvement. Thanks and bye.

20040910, 09:21 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: ways to reduce file links memory (office XP)
Happy I could help.
Concerning some of the other "questions/comments"
what do you mean by IFs are 'memory hogs'? They seem to use up a lot of memory. If possible (some IFs can not be done this way) "boolean" formulas are better. Again an IF in a named formula could reduce memory.
Are array formulas are the one that must be entered with Ctrl+Shift+Enter and a formula like =MIN(A2:A6000) is not an array, isn't it? The ones entered with "ctrlshift+enter" are termed "array formula", though technically min, max, average are also "array formulas" as is SUMPRODUCT, slope, etc. By themselves they are not a big issue. Many times they are used to get multiple criteria and these can involve lots of calculations and many repeat the same calcs so they can really slow excel down.
but i think builtin functions are those in list in excel by default and mega formulas are long list of functions combination and udf is new function created in vba Yes that is correct
Steve

20040917, 04:04 #5
 Join Date
 Aug 2004
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
hi Steve, thanks a lot for your advice. about the 'named formula', let say i have a formula that inputs are still variable, and the formula need to be copied down, far down to row6000, how am i going to do the 'named formula'? thanks again.

20040917, 07:51 #6
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
To replace a loooooong formula with a named formula, follow these steps:
 Put your cursor on the first cell that contains the formula;
 Select the entire formula in the formula bar and hit controlc to copy it;
 press escape to leave the formula bar
 Choose Insert, Name, Define;
 Enter a name for the formula;
 Tab to the Refers To box and clear it. Then hit controlv to paste in the formula you have just copied;
 In the first cell, change the formula to:
"=TheNameYouGave" (without the quotes of course)
 copy this new formula down to fill all cells that contained the long formula.
Alas the formula cannot be longer than about 245 character though, if it is, you need to split it up over more than one named formula and piece them together, either in the cell itself or in another named formula.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20040917, 11:43 #7
 Join Date
 Aug 2004
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
Hi Pieterse, i think i understand the named formula in that way, but, the first cell formula requires changes in its input, let say in G2, a formula goes =SUM((A2),(D2),(F2)) and i copy the first cell formula to G6000, 5 seconds later, i change the fomula on G2 to =SUM((A2),(B2),(F2)) and copy down to G6000, and 5 seconds later, i change the G2 formula again to do some kind of testing. with this, i think this named formula cannot fit this requirement, can it? either there is a better way to reduce this kind of formula memory or i just fix to this formula and copy to G6000, as original way? THANKS for any help.

20040917, 12:03 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
A named formula is very handy in just the situation you describe.
You only need to change the named formula and there is no more copying down involved, all cells that contain the reference to the named formula update automatically.
You do need to make sure you have one of the cells selected that contain a reference to the named formula, before you start editing the named formula.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20040917, 12:58 #9
 Join Date
 Aug 2004
 Posts
 103
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
hi Pieterse, sorry, maybe i do not understand your instruction on editing my formula. now, i hit F2 at G2 to provoke the formula on G2 out and i press left arrow key to edit my formula, just like from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)) and lastly press Ctrl+Shift+Down Arrow to complete the formula copies to G6000. about named formula to change =SUM((A2),(D2),(F2)) to =TEST, and =TEST appears in G2 until G6000, and when i need to change =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2)), do i have to go InsertNameDefine select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))? if that is the case, i will stick to my original way. Thanks again.

20040917, 13:16 #10
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: ways to reduce file links memory (office XP)
<hr> do i have to go InsertNameDefine select TEST and edit in the Refers to: column from =SUM((A2),(D2),(F2)) to =SUM((A2),(B2),(F2))?<hr>That is correct. In case of the formula you posted, I doubt if there will be much benefit in using a named formula regarding the size of the workbook.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association