Why does my broken link formula still works and does not return error?

[stewegg]

Hi,

I know this sound weird.
But, how could a broken link formula still return the correct result but error message?

I am using VLOOKUP formula to find a result from a workbook on a network drive.
It work great and everything.
However, what I found was when the network drive is disconnected, the VLOOKUP formula still works!
HOW? and WHY? It's not giving me any error where it suppose to?
I did close and reopen Excel; it did warm me about updating links and links can not be update and asked me if I would like to continue or edit links.
But the formula still works and still returns the correct result even know the links are no longer exist.
Is there some kind of temporary data or some sort saved in the original workbook?

Thank you very much,

[Nisha Dhawan]

Hi stewegg,

Thats nice that you can still see the result without connecting to drive. However, this happened because as you said, the moment you open file it asks you "Update the link" or "Continue without updates" (something like that :P). And when you click on "Update the link" it returns saying "Links can not be updated" and ask you to edit the links or continue. And when continue then in that case, the previous result is putted on last updated result. If you refer to the Excel Option by following steps ALT+T+O-->Formula-->Calculation Option (Check this) - The calculation option will be selected as 'Manual' instead of 'Automatic'.

Hope this will give you clear picture.

[stewegg]

Hi Nisha,

I check the Calculation Option under Formula, "Automatic" is still selected.

I kind of get what you are saying.
But something that does not quilt make sense is the size of the file.
The "database" workbook that's the network drive is about 5MB and the VLOOKUP workbook is only 520kB.
How could a 5MB data be "saved" in a 520kB workbook as a temp result file and still be functional?
It just doesn't make sense......

[stewegg]

By functional, I mean, VLOOKUP still returns the correct result doesn't matter what the lookup_value changed to....

eg. =VLOOKUP(A2, 'networkdrive://....//database.xls'!Data, 2, FALSE)

Doesn't matter what I change A2 to, it still give me the correct result.

[Nisha Dhawan]

No it won't happen if you the lookup value is not matching in lookup table. You must try using it on any excel book with sample data on excel itself. Then remove lookup value from master file and see the result it will return to Error #N/A.

[stewegg]

That was what I originally expected: to have an error showing up.
But instead, I got a correct result every single time when I change A2.
For sure if I would to make any changes to the "database" workbook, the VLOOKUP workbook will defiantly not reflect that change.
But I just could not figure out how and where does this "temp" data is stored, nothing make sense....

[Jonmo1]

The table that the Vlookup refers to is stored in the file containing the vlookup formula.

Where in the file, I have no idea.