Reference to external file breaks formulas on document open

[Lyndhurst]

Hi,

I have been working on a multiple spreadsheet Calc file filled with formulas based on references to another Calc file. References are made leveraging the “named ranges” functionality from the external file.

Everything works fine until I copy a sheet, or close then re-open the document. On opening the document, I have a pop up notifying me that I have references to external files, and prompting me if I want to update them.

No matter which option I choose, all references to the external file are broken in formulas, and Data Validity cell ranges I set up.

What I have read on the forum and does not fix the problem:

• Recalculate has no effect
• Ctrl + H to replace all “=” with “=” only fixes some formulas randomly

The only thing that works for “Data Validity” cell ranges, is to edit them, and hitting OK without making any changes. Of course this is unusable, and I cannot work around referencing an external file for my purpose.

Is there any trick, or macro to run on document load to fix this issue?
If not, would it be more reliable, or possible to use a registered data source like a Base table?

Thanks for your help, I would love to salvage the hours I put in that project

PS: I am using LibreOffice 6, and saving / referencing documents in .ods format.

[Lupp]

A workaround: The source sheets/documents automatically maintain (each) an array of the addresses currently equivalent to the named ranges, and the sheets pulling data use a respective indirection.

[Lupp]

My very first bug report nearly 8 years ago was a monster due to lack of experience - and also due to the rather complicated matter. I think it was about this issue.

See: tdf#61743
and there: https://bugs.documentfoundation.org/show_bug.cgi?id=61743#c12

My suggestion: Use references by explicit addressing. (Yes. There are cases where it cannot do what you want without complications…)
(Also: Avoid external references wherever possible.)

The bug is still “NEW”. Probably you want to “bump” it. Make a new and better-because-simpler example in this case.

[Lyndhurst]

Thank you for your answer. There are similarities between my case and the one described in the links you provided.

Except that from what I understood (correct me if I am wrong), the named range references stopped working as expected after a change in the documents paths. I also understood that the different documents were referenced through relative paths in your case. The document I created contains only absolute paths, and the simple fact of re-opening it messes up references.

Nevertheless, after browsing through your bug report, I decided to try addressing ranges by cell positions instead of named ranges, and it seems to solve the issue on closing/re-opening said document.

This would limit the possibility of adding data to the referenced ranges in a simple manner thus thwarting my attempt to use Calc as a simple database.

If I do not get any help or insight to solve this problem, I will try to find a workaround for the time being, and turn to a database oriented solution like Base later on.

Thanks anyway @Lupp for helping me debug this problem further.

PS: I tried to post this as a comment to Lupp’s answer, but the max length was exceeded. Maybe I should be more concise

[Lupp]

If a comment urgently needs to be longer than accepted, better split it in two (“follows…” / “…continued”). Misusing the answer tool for comments you can’t get a strable coordination in a Q&A site.
“Calc for database tasks” causes never ending discussions on https://forum.openoffice.org/en e.g. It generally isn’t a good idea. There may be rare exceptions where the database is clearly and reliably long-term-limitted to small amounts of data, and related topics like backuping, safety, security, standardization of queries, transaction locking… are minor due to very specific facts. Actually I would restrict the concept to one-person-data-for-creative-tasks. At least that’s the only case I used data-keeping spreadsheets successfully many years ago.
The case having induced the mentioned bug report is too long ago to tell more about it from memory. Restarting it would require a new approach for me due to shortcomings of my organic RAM wich is more Random than Access…

[Lyndhurst]

You are right about the use of the comment section, and I should have thought myself about splitting it. Sorry about that.
You might be right again about my use of Calc as a small database being the real issue. So I will close it, my intention was not to bloat the forum. I did not fell the little project I was working on was worth going through the process of normalizing the data to build a relational database.
Thanks again for your help and advice.

[Lupp]

As I already mentioned, I also used data-keeping spreadsheets for “creative” (optimizing) tasks.Your reasons may be different. My opinion: We shouldn’t work with declarations like “Every kind of data-keeping must regard third normailzation at least” or “Macros are evil.” or …
What we need are conscious and informed decisions.
However: Lupp’s statements “US-American date formats are a mess.”, “So are ‘imperial units’.”, “Nobody should use the comma or the point as a group separator formatting numbers.” are undisputable