We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

vlookup link to external file sometimes lost on load [closed]

asked 2015-03-18 11:22:19 +0200

tim gravatar image

updated 2020-08-17 17:44:07 +0200

Alex Kemp gravatar image

I have a few Libreoffice OpenDocument spreadsheets which all retrieve the latest share prices and forex rates from some CSV files in another folder using vlookups. These CSV files are updated by a script which I run before I open the spreadsheets:

=VLOOKUP("USDAUD=X",'file:///home/username/Documents/Share Research/Data/Forex Data Downloaded Data.csv'#$Sheet1.$A$1:$B$15, 2,0)

When I open the spreadsheets LibreOffice always prompts with "This file contains links to other files. Should they be updated?", which I always click yes to.

This works fine about 8 out of 10 times. But sometimes I open the spreadsheets to find that, seemingly at random, some of the vlookup cells have lost the file path to the CSV files and instead look like this

=VLOOKUP("USDAUD=X",, 2,0)

I haven't moved or deleted any of the files involved in this at any time. Does anyone have any suggestions how to avoid this problem? Or if there's a fix in a future version?

libreoffice-calc 4.2.7-0ubuntu2 on Ubuntu 14.04

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-17 17:44:35.681052

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-18 11:39:47 +0200

oweng gravatar image

The Link to external data feature has long been flaky in various ways. This seems like it may be one more case of this. Bug fdo#87973 describes the same issue for XLSX and links fdo#63992, which describes the same for ODS. The latter has been marked as a duplicate issue to a more general XLS issue by a Calc developer.

edit flag offensive delete link more

Comments

Ok thanks, I've upgraded to Libreoffice 4.4, I'll see if the problem re-occurs with that version

tim gravatar imagetim ( 2015-03-19 11:16:52 +0200 )edit

A month so far on Libreoffce 4.4 and so far the problem hasn't re-occurred, so hopefully this is something that's been fixed somewhere between LO 4.2 and LO 4.4

tim gravatar imagetim ( 2015-04-19 10:35:05 +0200 )edit

I should've updated this before, but the Link to External Data feature is completely broken, even in recent Libreoffice versions. It'll fail silently and randomly.

The only thing that works is to have the data as CSV files and then to Sheet->Insert Sheet From File.. and use the 'Link' option (so that it updates automatically each time you load Libreoffice). You can then hide the sheet if you like. This has worked well for me so far. See https://help.libreoffice.org/Calc/Ins...

tim gravatar imagetim ( 2016-11-17 12:16:31 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2015-03-18 11:22:19 +0200

Seen: 889 times

Last updated: Mar 18 '15