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?

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?

It does a recalculate, and all of those formulas recalc at once some systems
it is fast and some slow, you can try going to options and turning off
automatic calculations, but you have to remember to calculate when you need
answers.

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Marilyn" wrote in message
...
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?

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?

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?

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?

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?