VLOOKUP with restricted access rights

[DaveO]

Periodically I receive a worksheet from a colleague in which he uses
VLOOKUP functions that reference files in his department's restricted
server storage area. (Each department has a secured sharename on a
server, accessible only to registered users.) I import this tab in its
entirety to a workbook I use.

A sample VLOOKUP formula is
=VLOOKUP(D16,'\\server name\share name\Directory\sub directory\sub
directory\[filename.xls]Sheet1'!$A$4:$E$359,2,FALSE)

When I open the file I am prompted to update external references. If I
click "update" I get an error, "Workbook contains links that cannot be
updated". If I click "continue" I can work on the file; if I click
"Edit links" I see the files that are referenced with a notation
"Error: source not found". I can see the shared name on My Computer
but cannot access it; I can see it from a command prompt but I get
"access denied" responses when I try to list files on that share name.

The weird part is: if I give one of these VLOOKUPs a new lookup_value,
I get an answer, rather than a prompt for a password or "access
restricted" message. The substantially weird part is: I can close the
file and close Excel, unplug my network connection, reload the file,
use a new lookup_value and STILL get an answer.

Is Excel somehow referencing files somewhere on my hard drive? This
appears to be a security lapse on the network, but getting an answer
when I'm physically disconnected from the network suggests otherwise.
Any ideas?

Thanks

 

[Harlan Grove]

The weird part is: if I give one of these VLOOKUPs a new
lookup_value, I get an answer, rather than a prompt for a
password or "access restricted" message. The substantially weird
part is: I can close the file and close Excel, unplug my network
connection, reload the file, use a new lookup_value and STILL get
an answer.

....

Excel caches data from external references in the file containing the
external references. This is just one of (way too) many things that
can make Excel workbooks huge.

 

[DaveO]

Thanks, Harlan, that makes sense.