Find & Replace vs Cut & Paste

Mar 11th, 2002, 11:16 AM

I have a massive collection of formulas (250 cols x 900 rows) which must be calculated on a series of quarterly data. The quarterly data is 20 columns and usually about 50000 rows.

I've tried several methods looking for a fast way to do this. The recalculation time is enormous. I don't know if it's just because of the number of formulas and that most of them are various dcount and daverages. On a 433 mhz pentium 3, this is taking about 2 hours to calc.

I've tried pasting in formulas where they all refer to a database name which is the same in each spreadsheet. I've also tried pasting the formulas in with direct cell references (however, this requires turning them into text and then using find and replace to turn them back into formulas. Anything else seems to mess up some of the references).

I haven't yet tried pasting the data into a spreadsheet which already contains the formulas and hitting calc.

I have also run into out of memory error on some spreadsheets despite about 200 meg of free ram and about 1.5 gig of free disk space.

Suggestions? Is something with Excel's undo slowing this down? Is it something with the clipboard?

Mark W. · Mar 11th, 2002, 11:54 AM

Take a look at...

http://support.microsoft.com/default...b;EN-US;q72622

Also, consider moving some of your infrequently updated data to a separate workbook that you keep closed and use absolute references to that document.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 11:16 AM	#1
Guest Posts: n/a	I have a massive collection of formulas (250 cols x 900 rows) which must be calculated on a series of quarterly data. The quarterly data is 20 columns and usually about 50000 rows. I've tried several methods looking for a fast way to do this. The recalculation time is enormous. I don't know if it's just because of the number of formulas and that most of them are various dcount and daverages. On a 433 mhz pentium 3, this is taking about 2 hours to calc. I've tried pasting in formulas where they all refer to a database name which is the same in each spreadsheet. I've also tried pasting the formulas in with direct cell references (however, this requires turning them into text and then using find and replace to turn them back into formulas. Anything else seems to mess up some of the references). I haven't yet tried pasting the data into a spreadsheet which already contains the formulas and hitting calc. I have also run into out of memory error on some spreadsheets despite about 200 meg of free ram and about 1.5 gig of free disk space. Suggestions? Is something with Excel's undo slowing this down? Is it something with the clipboard?

Mar 11th, 2002, 11:54 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Take a look at... http://support.microsoft.com/default...b;EN-US;q72622 Also, consider moving some of your infrequently updated data to a separate workbook that you keep closed and use absolute references to that document.