LO Calc: relative path to external sheet?

[az2000]

How can I express the following:

'file:///home/mark/Desktop/L4L/Products/material costs.ods'#$Webbing.$C$2:$C$6

as a spreadsheet in the same directory? I.e., no absolute path. Example:

'file://material costs.ods'#$Webbing.$C$2:$C$6

I’ve tried various things. LO keeps changing it to an absolute path. I have click a different cell and then click back to the one I edited to see how LO reverted it to an absolute path.

The only reason I’d like it to be relative is to make my formula’s less tedious. (They’re pretty busy-looking with the same path 4-5 times in one formula.).

Thanks in advance.

[hjek]

Yes, you can do that:

=INDIRECT("'./material costs.ods'#$Webbing.$C$2:$C$6")

I guess another use case for this would be sending projects with more than one spreadsheet to other people without broken references.

(If this works for you, please click on the ✓ to accept the answer.)

[az2000]

Thank you so much for your answer. I have accepted it. But, I have to say that seems like not a good solution. I wish LibreOffice had a more elegant way of doing this. That will look as “busy” (multiple times in a cell’s formula) as the actual path. But, I can see how your solution will be more portable. So, that’s good. (My issue was more about the business of the absolute path appearing 4 times in the same cell. A relative path would make the cell more readable.). Thanks!

[hjek]

Yes, adding another function call doesn’t make it look less busy.

Also, it appears that this question has been discussed previously:

The reference is always shown absolute. It will nonetheless be saved relative to the document if the respective option is chosen.

So, apparently you may be using relative paths anyway. Not sure if that helps.