Way to prevent VLOOKUP from verifying remote file exists?

  • Thread starter Thread starter Mike Frederick
  • Start date Start date
M

Mike Frederick

Is there a way to stop the VLOOKUP function from accessing a remote file
which is listed as the lookup table? I have a cell like:

=VLOOKUP(C3, "C:\users\mike\[lookup.xls]...

and I need to change the reference to the external file to a different file
(that doesn't yet exist). Excel gets concerned and displays a dialog (which
I suppress via .DisplayAlerts = false) but still tries to locate the file
(which is time-consuming). I have tried setting calculation mode to manual
(Application.Calculation = xlCalculationManual) and disabling remote link
updating. Does anyone know how to stop this behaviour? TIA
 
Mike

I don't know how to prevent that. One thing you could do is to precede the
formula with an apostrophe. That will make the formula just a text string
in the cell. Then, once the external file is created, you could remove the
apostrophes.
 
Back
Top