When you copy and paste data that are formula calculations, the formula needs
to re-iterate through all the calculations. See here for more information:
http://www.decisionmodels.com/calcsecretsb.htm
One possibility is to paste the values, rather than the original formula.
To do this, select the cells you want to copy, copy, and then choose Paste
Special--Values. Note, however, that if you choose this option, you will no
longer be able to recalculate the formulas.
Dave
--
Brevity is the soul of wit.
"Marilyn" wrote:
My question is will this occur even when copying and pasting data between
sheets. I open the original file which was created last year and I dont have
a problem copying and pasting. the only difference is in the Vlookup
formula. The orginal formulas look like this:
=VLOOKUP(A2,'Web Focus'!$A$1:$F$9222,1,0)
=VLOOKUP(A2,'Web Focus'!$A$1:$F$9225,2,0)
=IF(VLOOKUP(A2,'Web Focus'!$A$1:$F$9225,3,0)="","",VLOOKUP(A2,'Web
Focus'!$A$1:$F$9225,3,0))
the original file is built with the same amount of rows and sheets and it
does not stall when I copy and paste in that file. I see the calculating
message within the status bar, however, it does not take that long.
thanks for the response :-D
"Dave F" wrote:
You have about 30,000 rows to which VLOOKUP is being applied.
If the calculation is taking too long to bear, you have the following options:
1) Use only one VLOOKUP at a time by placing an apostrophe to the left of
the = sign in the VLOOKUPs you do not want to run. The apostrophe turns the
formula in question into a text string, so it doesn't calculate.
2) Install more memory on your computer. For reference I just ran VLOOKUP
against 65,000 rows of data and it took my computer a good fifteen seconds,
and that is with a dual-core processor and 2.5 gigabytes of RAM. You're
asking a lot of Excel with those formulas!
Dave
--
Brevity is the soul of wit.
"Marilyn" wrote:
I'm currently working with an Excel 2003 file. it contains 3 sheets and most
of the sheets contains 9424 rows of data. One sheet has the following
formulas:
=VLOOKUP(A2,V1_Forecast_Version!$A$1:$F$9241,1,0)
=VLOOKUP(A2,V1_Forecast_Version!$A$1:$F$9241,2,0)
=IF(VLOOKUP(A2,V1_Forecast_Version!$A$1:$F$9241,3, 0)="","",VLOOKUP(A2,V1_Forecast_Version!$A$1:$F$92 41,3,0))
The problem is that when I copy and paste data from one sheet to another
Excel displays Calculating Cells 5%, 10% and it takes a long time to get to
100%. I cant do any other work while this is happening. I have been looking
around for the answer but none of the solutions that I have seen works for me.
Can someone please help?