Ask Your Question
0

Referencing a Cell in other Document using Relative Path

asked 2017-03-12 22:35:34 +0200

nachtigall gravatar image

updated 2017-03-12 22:36:31 +0200

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
3

answered 2017-03-12 23:00:10 +0200

Lupp gravatar image

updated 2017-03-13 19:58:10 +0200

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.

edit flag offensive delete link more

Comments

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.

nachtigall gravatar imagenachtigall ( 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.

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

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

nachtigall gravatar imagenachtigall ( 2017-03-13 22:49:44 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-03-12 22:35:34 +0200

Seen: 1,583 times

Last updated: Mar 13 '17