Whey is Excel 2003 calculating take so long - sometimes 20 minute

[Guest]

I have a lot of vlookups and I have tried everything. The data is one the
same workbook, the data is copy past value so that it isn't tying to
anything. I don't have any links on this sheet, but sometime it takes 20-40
minutes to calculate. It doesn't tie up the machine, but it shouldn't take
that long

 

[Niek Otten]

Please post your formulas. Is the data sorted? What values are you looking
for? How big are the ranges?

For good advice on performance (especially for lookups), visit

www.decisionmodels.com

 

[Guest]

There are 35 sheets of 100 columns and 120 rows of vlookups. I have 512 MB
of ram so there should be no problem with memory

 

[Guest]

That's 420,000 VLOOKUPs?! I think some examples of these would be pertinent
to help you find another solution instead of the VLOOKUP's.

Kevin M

 

[Guest]

When you "Pase Special->Values", even though the information you are pasting
isn't calculating, Excel re-calculates everything in the spreadsheet (if you
have automatic calculation turned on). Try turning automatic calculation
off: Tools->Options->'Calculation' tab->check 'Manual'

 

[Guest]

There are 35 sheets of approximately 100 columns and 120 rows of vlookups.

 

[Guest]

Dave, that will solve the problem but not the cause. As soon as he does
calculate it'll take twenty minutes again. There has to be more to this
problem... If he's got say, 10,000 VLOOKUPs it's a cause for concern.

Kevin M

 

[Guest]

Emom, how many VLOOKUPs are we talking here? What kind of hardware?

 

[Niek Otten]

EMOM,

WHY NOT JUST ANSWER THE QUESTIONS OF THOSE WHO TRY TO HELP YOU?

"Please post your formulas. Is the data sorted? What values are you looking
for? How big are the ranges?"