You could try it, though I'm not sure how that would address the larger issue
that you're running VLOOKUP on tens of thousands of rows.

Dave
--
Brevity is the soul of wit.


"Marilyn" wrote:

Dave,

would a Dynamic Range name help me out with this file?

thanks,


"Dave F" wrote:

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?