# Referencing a Cell in other Document using Relative Path

How can I enter a reference to another file using a relative path?

At the moment. As soon as I enter

='otherfile.ods'#$Sheet1.A2  this is converted immediately to ='file:///home/jens/Dropbox/parentfolder/otherfile.ods'#$Sheet1.A2


by LibreOffice Calc.

The idea is that I can move the parent directory around (it is in a Dropbox) and the references are still intact.

I do not see any docs about it at the official References to Other Sheets and Referencing URLs. Searching the web reveals some notes to enable 'Save URLs relative to file system' under 'Tools' > 'Options' > 'Load/Save' > 'General' > 'Save', but it is already enabled on my computer.

This is with LibreOffice 5.1.6.2 on Ubuntu 16.04.

edit retag close merge delete

Sort by » oldest newest most voted

The reference is always shown absolute. It will nonetheless be saved relative to the document if the respective option is chosen. Of course, both the files must be located under a common node of a file system tree.

Editing with respect to the comments below:
I only can advise to avoid the usage of named ranges when referencing across sheets. Their usefulness is overestimated anyway. This, imo, at least, if the range is not glued together with additional incredients creating a useful expression.

more

Seems to work for normal cell ranges (e.g. \$Sheet1.A2), but not when using a named range? I want to use this for Data > Validity > Allow: Cell Range > Source: 'otherfile.ods'#named_range. But this is not working anymore as soon as the parent folder is moved. Even entering ='otherfile.ods'#named_range directly in a cell does not work this way.

( 2017-03-13 12:34:54 +0200 )edit
1

I reported bug tdf#61743 about this. Given my knowledge at the time about the tdf bugzilla and also about some technical relations this was too difficult for me and did not succeed. A QA volunteer (meanwhile retired; thanks!) confirned the bug nonetheless. There was an additional report in the same thread on 2016-06-30 meanwhile. You should try to do a better job than I did.

( 2017-03-13 19:53:54 +0200 )edit

Wow, thanks for the insights! You are right, I'll simply avoid named ranges then.

( 2017-03-13 22:49:44 +0200 )edit