I posted this on bugzilla a couple of months ago.
https://bugs.documentfoundation.org/show_bug.cgi?id=132133
Regrettably, there seems to be no generally-acceptable solution. Furthermore, according to bug 88108 which I cited, the difficulty is related to the odf file structure; I ask myself why that fault was allowed to become part of a specification that seems to be difficult to modify.
This may appear to be a relatively trivial issue, but it is one that renders LO totally unusable for many educational and business purposes. I’m seriously worried that the FOSS movement (including recent developments in Munich) will be compromised by such failings.
I understand from your final comment that, in your situation, writing macro software is not an allowable workaround. This is usual in many regulated and other working environments. However, there are many non-critical cases where Microsoft’s VBA macro development environment can be used, for example in education (making flashcards…). VBA has a huge ecosystem on the web where you can usually find suitable macros and code snippets (there are plenty of flashcard macros to choose from). The LO macro environment has a learning curve that makes it unacceptable for amateur and occasional users, thus reinforcing the dominance of Excel.