Jan 30 2019 06:17 AM
I have an .xlsx file that contains data from another excel spreadsheet like this:
=VLOOKUP("8 2",'C:\Users\Username\AppData\Local\Microsoft\Windows\INetCache\Content.Outlook\EMQN5Z28\[1. HSP_2012_2018 bck only.xlsx]Direct from Access'!$B$2:$H$23,6,FALSE).
The problem is that the specified file does not exist, and the folder EMQN5Z28 does not exist either, but the data from this non-existing file appears in the cells that refer to this excel file. Has anybody had such experience?
Jan 30 2019 07:45 AM
It appears the link was created from an Excel file opened from an Outlook attachment. The attachment is downloaded to this hidden folder before opening. The user ('username' in your example?) should have saved the attachment before creating any links.
Jan 30 2019 09:03 AM - edited Jan 30 2019 09:06 AM
You absolutely right, it most likely was an Outlook attachment. Strange thing is that the formula still works, even though I am not able to locate the file with the actual data. I copied the spreadsheet in another .xlsx file, I copied the formula in another cell, I tried to change parameters in the formula and it gives the right numbers. I am not worried about the data, since I have them, but I was just wondering how it could be that the data comes from non-existing file. I checked the Content.Outlook folder, there is no such a file there. I tried search option for the file in Windows directory, it returns nothing, but the numbers still come from nowhere. That's surprising!
Jan 30 2019 10:07 AM
I found one more thing, maybe that's the reason:
"Functions such as LOOKUP and VLOOKUP create and store a hidden cache of the source data within the Microsoft Excel file that can be exposed through careful manipulation of the function (e.g. manually editing the column index within the VLOOKUP function will return different values from the cache)".
Any idea how to see the whole cache content?
Jan 31 2019 08:15 AM
Not too sure how to view the cache.