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 Frederick

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Mike Frederick" wrote in message
...
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 Frederick