MYSTERY!!

W

WhytheQ

we have a spreadsheet with a load of Vlookup formula in it.
if it is opened when no other spreadsheet is open in Excel then excel
has a lot of trouble opening the file: the application looks like it is
seraching for something.
if any other spreadsheet is open before opening the offending workbook
then it opens fine!!

any one ever experienced this before?

any help greatly appreciated.
Jason.
 
M

Mallycat

First make a copy as a backup. Then open the spreadsheet on its own.
Go to the Excel menu EDIT\LINKS and see if any other spreadsheets are
linked to it. You can break the links if they are there

Matt
 
B

broro183

Hi Jason,
just to add to Matt's response...

If you intend for the vlookups to refer to another file then you
probably won't want to break the links but if you open the other
(referred to) file first then the process of opening the slow file
should be quicker.

Also, you mention that you have a "load of vlookups", if you have used
any in IF statements, eg
=IF(vlookup(blah...)=xyz,abc,vlookup(blah...)), I'd recommend splitting
the vlookup into a separate cell (eg C1) and changing your IF
statements to =IF(C1=xyz,abc,C1). This is effectively halving the # of
vlookups that are calculated each time.

The following links may also help identify reasons for speed issues:
http://www.mvps.org/dmcritchie/excel/slowresp.htm
http://www.decisionmodels.com/optspeedb.htm
http://www.decisionmodels.com/calcsecrets.htm


hth
Rob Brockett
Always learning & the best way to learn is to experience...
 
Top