LibreOffice Calc INDIRECT links break for 2 Spreadsheets in the same folder

[Panorama3464]

Greetings, dear friends

I’m facing an issue with external links in LibreOffice Calc that’s been quite frustrating. I have two .ods files located in the same folder. One file is supposed to load values from the other using formulas like:

=INDIRECT("'./spread1.ods'#$Export.A8")

However, every time I open the spreadsheet that loads values (indexer.ods), I have to manually fix the external links. LibreOffice Calc prompts me to select the file (spread1.ods) again, even though both files are in the same folder.

I suspect it might have something to do with how LibreOffice handles relative paths or updates external links. I’ve tried using absolute paths in the formulas (file:///full/path/to/folder/spread1.ods), and it works, but these spreadsheets are supposed to be in a version control system, editable and working for anyone with access, at any location - so absolute paths would break this integration

Could someone please advise on how to resolve this? I would like a solution that prevents LibreOffice Calc from requiring manual intervention to update these external links every time I open the file, as I’m loading from many external files into 1 single “indexer” file.

Thank you in advance for your help!

[karolus]

hallo
Try:

=INDIRECT(REGEX(CELL("FILENAME"); "^(.*/)";;1)&"spread1.ods'#Export.A8")

or alternativly:

=DDE("soffice";REGEX(CELL("FILENAME");"^('file://)(/.*/)(.*$)";"$2")&"spread1.ods";"Export.A8")

[Panorama3464]

Thank you very much!

The first formula worked perfectly!

[mariosv]

INDIRECT("'spread1.ods'#Export.A8")
should work, taking care of the single quote before the file name.