[Solved] VLOOKUP not retrieving info

Discuss the spreadsheet application

[Solved] VLOOKUP not retrieving info

Postby TalonsPryde » Sat Jan 19, 2019 7:52 am

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.
Last edited by TalonsPryde on Sat Jan 19, 2019 10:10 pm, edited 1 time in total.
Open Office 4.1.5 on windows 7 with sp1
TalonsPryde
 
Posts: 4
Joined: Sun Nov 04, 2018 8:41 pm

Re: Vlookup not retrieving info

Postby Zizi64 » Sat Jan 19, 2019 8:26 am

- 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.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7825
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Vlookup not retrieving info

Postby TalonsPryde » Sat Jan 19, 2019 9:55 pm

Local file...
file open to get spread.
I will get a sample file set soon...
Open Office 4.1.5 on windows 7 with sp1
TalonsPryde
 
Posts: 4
Joined: Sun Nov 04, 2018 8:41 pm

Re: Vlookup not retrieving info

Postby RusselB » Sat Jan 19, 2019 10:01 pm

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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4923
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Vlookup not retrieving info

Postby TalonsPryde » Sat Jan 19, 2019 10:09 pm

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.
Open Office 4.1.5 on windows 7 with sp1
TalonsPryde
 
Posts: 4
Joined: Sun Nov 04, 2018 8:41 pm

Re: Vlookup not retrieving info [Solved]

Postby RusselB » Sat Jan 19, 2019 10:38 pm

Well, you don't have to close it, but the calling file will only work with the data that is in the saved file.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4923
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests