Apache OpenOffice Community Forum

I am trying to use vlookup with an outside file. When I reference the file, I put in my term (I used "Test") and have 2 columns after to retrieve data from. All I get is either a blank cell or #N/A even though everything is exactly alike (cell format, text, and source to result). I have search criteria applying to whole cells activated. This is frustrating me to no end. I am effectively trying to do a grocery list for shopping referencing a master grocery list file.

My wife is an accountant and uses vlookup regularly in Excel and helped me figure out how to reference external files.

- Are you using absolure or relative URL-s for the another file?
- Is the another file open?

Please upload two ODF type sample files here with some sample data and the formula what you tried to achieve.

Local file...
file open to get spread.
I will get a sample file set soon...

Please note that the format of the VLOOKUP function in Excel, when accessing a secondary source, is different from that of the same function in Calc.
Also please note that, with later versions of Excel, if the secondary source is an open file, Excel uses the data of that open file, rather than the data of the most recently saved version of that secondary source.
Calc (in OpenOffice at least, not sure about Calc for LibreOffice) only accesses the data of the most recently saved version of the secondary source.

So you have to have the secondary source (outside file as you called it) saved before your active file (the one with the VLOOKUP) can read the data, and the data has to be valid.

Oh wow. That really helps. So I have to save the file first, close it, then refresh the calling file to get it to work. I had no idea. Thank you so much.

Well, you don't have to close it, but the calling file will only work with the data that is in the saved file.