Calc - Update external / internal links - locked cells - problem

[Albireo]

Hi!
Have created a number of .ods files (Calc) with mainly formulas and diagrams. each file has a name and ends with a year (eg file 2022.ods, file 2023.ods, file 2024.ods) and so on.
Each file contains 1 tab for each week (52-53 tabs).
This formula in B1 keeps track of which year, the file deals with (sorry instructions in Swedish)
=TEXTNUM( EXTEXT(CELL("FILENAME"); HITTA(".";CELL("FILENAME"))-4; 4) )
The first tab contains one link to a cell from the previous year.
=OMFEL(INDIREKT(SAMMANFOGA(VÄNSTER(CELL("FILENAME"); HITTA("."; CELL("FILENAME"))-5); B1-1; ".ods'#$'52'.R1")); "Fel")
The last tab in the file contains twelve cells that point to twelve cells in the file the following year. Like this.
=OMFEL(INDIREKT(SAMMANFOGA("'Likviditet "; $J$1+1; ".ods'#$'"; VÄNSTER($P21;1); "'.S19")); "Saknas") If the file is missing, it says “Saknas” (missing) in the cell.
All tabs are locked, but some cells on the tabs, which can be used, are not locked.
Updating the cells always worked before, but now the cells are not updated every time the file is opened.
Each time one of the files is opened, the user has to press the “Allow update” button.

Question 1
Sometimes it is enough to unlock a tab for the cell to be updated. If the tab is locked again, the external value is lost.
There are cells in tab B that refer to a cell in tab A - sometimes the value is not updated between the tabs, but if the cell is unlocked, an update occurs. (This problem does not always occur)
What have I missed?

Question 2
Moved the .ods files from one directory/computer to another directory/computer. When updating the links, a message appears that the source directory is missing.
Is there a way to find the cell that is trying to establish this missing external link?

Question 3
Suppose the files 2020, 2021, 2022, 2023 are in a directory.
All files have external links between each other.

• Does it matter to have so many external links?

If the 2020 file is opened and a change is made.

• Will the 2023 file be updated with that change when opened?

Question 4
Is it possible to automate the updating of external links? (without having to press the “Allow updating” button)

[erAck]

It could help if you switched your formula language to English (Tools → Options → Calc → Formula, Formula Options, Use English function names) to post examples, otherwise no one will know what HITTA, OMFEL or VÄNSTER would be. Or best edit your question and attach a sample file, so everyone can see in their preferred language.

[schiavinatto]

The annual files are large, could they be combined in a single file?

[erAck]

@Albireo

Question 4
Is it possible to automate the updating of external links? (without having to press the “Allow updating” button)

1. Tools → Options → LibreOffice → Security, Macro Security:
   Security Level: Very high
   Trusted Sources: Add…, create/pick a trusted directory
2. Tools → Options → LibreOffice Calc → General,
   Update links when opening: Always (from trusted locations)
3. Place the linking file(s) (files that link to another file) in the trusted directory.

[Albireo]

Thanks!
Not sure if test files would help anything (they have a quite complicated structure with many formulas)

Because the desire with this spreadsheet is a continuous process - the weeks just roll on with no end in sight.
But for practical reasons I divided the weeks by year.
(files become slow to open and save) Previously with worse computer capacity there were two ods files every year

Have thought about how/if it would be possible to use a database instead, but haven’t figured out how it could be handled in a simple and smooth way.

This formula in B1 keeps track of which year the file deals, with the formula on the first tab.:
=VALUE(MID(CELL("FILENAME";A1);FIND(".";CELL("FILENAME";A1))-4;4))
(cell B1 contains e.g. 2022)

The first tab also contain one link to a cell from the previous year (another spreadsheet).
=IFERROR(INDIRECT(CONCATENATE(LEFT(CELL("FILENAME"); FIND("."; CELL("FILENAME"))-5); B1-1; ".ods'#$'52'.R1")); "Fel")
(cell B6 can contain 1 110 296 kr or Fel (Error) )

The last tab in the file contains twelve cells that point to twelve cells in the file the following year. (another spreadsheet) Like this .:
=IFERROR(INDIRECT(CONCATENATE("'Likviditet "; $J$1+1; ".ods'#$'"; LEFT($P20;1); "'.S19")); "Saknas")
(the content of that cell is either an amount eg. 123 321 kr or saknas (missing) )

So far - a description of the problem with English instructions

• . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . -

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

[erAck]

Please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead. Thanks.

[Albireo]

Maybe, I have found the problem?
In the cells that link to external cells, only the filename is created, not filename with path.
Maybe that’s my problem?

Assume the following
File name .: “Lik 2023.ods”
Tab Name “52”
Cell.: “Q20”
To be linked to
Filename .: “Lik 2024.ods”
Tab Name.: “1”
Cell.: “Q19”

The formula I’m trying to recreate is .:
’file:///C:/Users/ … /Lik 2024.ods’#$‘1’.Q19

But this formula =‘Lik 2024.ods’#$‘1’.Q19
Give the same result (If the formula is entered manually)
The formula changes automatically, to file name with path
(maybe this is a problem when the tab is locked?)
I’m trying to write a general formula that changes automatically when the file is renamed.
(eg copy the file Lik 2024.ods to Lik 2025.ods)
Both/all ods-files are always in the same directory.
I don’t want to enter the full path to the file
(it’s easy to move all files between directories and they still work)

Maybe this is the problem that it doesn’t always work for me?

[erAck]

That does not fit with the formulas you gave in Calc - Update external / internal links - locked cells - problem - #5 by Albireo, specifically for the 3rd
=IFERROR(INDIRECT(CONCATENATE("'Likviditet "; $J$1+1; ".ods'#$'"; LEFT($P20;1); "'.S19")); "Saknas")
that says the file name starts with Likviditet but here you say it should be just Lik and I assume that in $J$1 would be a year value like 2022 but what is in P20? With an arbitrary value of 1 the result of the CONCATENATE() expression is
"'Likviditet 2023.ods'#$'1'.S19"
Having created such file with a sheet named 1 and a value in S19 your example formulas just work. You can also try a simple
=INDIRECT("'Likviditet 2023.ods'#$'1'.S19") to verify.


You can use the Function Wizard (Ctrl+F2) on a formula cell to inspect the Structure tab and with Next and Back buttons interim results of expressions, or while editing a formula cell select a subexpression and press F9 for an interim result of that subexpression, to find where it derives from your expectation.

[Albireo]

Thanks (on how to use this forum)
Hope this way was more correct?
I think it’s quite a tricky forum - too much happens “automatically” and too few options - Enough about this.)
exuse me about the different filenames.

Yes - =INDIRECT("‘Likviditet 2023.ods’#$‘1’.S19") works,
Immediately “Enter” is pressed after the formula is entered, it changes to the full path to the file.
like this .: ’file:///C:/Users/ … /Likviditet 2023.ods’#$‘1’.Q19 (The file path has been shortened in this example)
Could it be because the tab is not locked? the formula usually works without problems when the tab is not locked.
Before the formula works, it seems to be rewritten and it sometimes works and sometimes it doesn’t.
Retrieves values when the tab is unlocked and does not retrieve values when the tab is locked.
When the “update links” button is pressed, sometimes a message appears that the link could not be established (even though it should be able to), but the path shown is to “my documents” - a default path in LibreOffice? If my programs were placed in that directory maybe it would work better?

Manually hard-coding the formulas, which file and cell should be read from another spreadsheet does not solve my desire.

1. Every time a new file (“Likviditet 2024.ods”, “Likviditet 2025.ods” …) is to be created and filled with base content, I have to manually rewrite new formulas in all places, to work.

My desire is to write a formula, which in turn creates a dynamic formula that is customized for this particular spreadsheet.
“Likviditet 2023.ods” needs information from both “Likviditet 2022.ods” and “Likviditet 2024.ods”.

2. If the spreadsheet “Likviditet 2024.ods” doesn’t exist I got an error. Instead of that error I want the text “Saknas!” (Missing). Then the person using the spreadsheet contacts me (and I fix that problem)

3. Right now I’m looking for a way to write a “dynamic formula” with path to the different worksheets.
   But having trouble splitting the file name into path and the name of the file.
   (eg. the path .: ’file:///C:/Users/ … / and the filename .: Likviditet 2023.ods)
   I can’t find an instruction that searches from right to left for the first “/” (actually the last “/” in the file name with path). Any Idea how to do that?

[Albireo]

These problems didn’t exist at all 5-10 years ago - don’t know which version of LO it started.
Also haven’t checked if Open Office has the same problem with updating cells from external programs or other tabs.

• . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ .

Can face the problem in a new way

As I mentioned earlier, there is a tab for each week. (approx. 52 tabs)
Almost all weeks (tabs) have the same formulas.

Cell H1 shows the week that the tab is about, based on the name of the tab with the following formula .:
=VALUE(MID(CELL(“FILENAME”);FIND("#$";CELL(“FILENAME”))+2;LEN(CELL(“FILENAME”))))
(This formula has always worked - even today.)

But this formula in Cell B1
=INDIRECT(CONCATENATE("’";$H$1-1; “’.R1”)) can work sometime, but not always.
The formula is in Cell B1 in all tabs, (except tab “1”.)
When problems occur in Cell B1, the problem is found in Tab2 and the #VALUE appears in that cell and it give error message in the rest of the tabs…

Most of the time, unlocking the tab is enough for the problem to disappear.
Sometimes the problem returns when the tab is locked again, but sometimes the error message does not return, while the file is open.
In tab 2, cell B1, the following formula must be found .: =$‘1’.R1
In tab 3, cell B1, the following formula must be found .: =$‘2’.R1 (and so on)
and as I said, I try to reproduce these formulas with this formula .:
=INDIRECT(CONCATENATE("’";$H$1-1; “’.R1”))
Is it possible to do the same thing in another way?

[erAck]

@Albireo

Yes - =INDIRECT("‘Likviditet 2023.ods’#$‘1’.S19") works,
Immediately “Enter” is pressed after the formula is entered, it changes to the full path to the file.

No, it does not. That happens only if you enter a direct reference like
='Likviditet 2023.ods'#$'1'.S19
without it being a text string for INDIRECT().


And please don’t use those ** bold formattings for formula expressions here as the rendering replaces quote characters to typographic quotes if not marked as code, so copy-pasting an expression to a spreadsheet will fail. Read the code formatting section I mentioned when adjusting the expressions in your other “answer”. If you want to also use bold formatting then use

**`expression`**

@Albireo

Could it be because the tab is not locked? the formula usually works without problems when the tab is not locked.

You mean because the tab/sheet is (not not) locked? I tried, but no, also if the sheet is locked both direct and indirect external references update their values when a recalculation is triggered.


Without a set of short sample documents that reproduce the faulty behaviour this all gets foggy and is poking around in nebular.

@Albireo

I can’t find an instruction that searches from right to left for the first “/” (actually the last “/” in the file name with path). Any Idea how to do that?

For example with =CELL("filename") in A1:
For the scheme and path:
=REGEX(A1;"^'(.+/)[^/]+'#\$.+";"$1")
For only path:
=REGEX(A1;"^.+://(.+/)[^/]+'#\$.+";"$1")
For the file name:
=REGEX(A1;"^'.+/([^/]+)'#\$.+";"$1")

(you must not name a file containing the sequence '#$ for this to work…).

[Albireo]

Thank you! (will try to improve my writing)
Intresting…
You seem to have a good understanding of how Regex works.

Something that puzzles me is - (you won’t believe me)
When I copy the formula =INDIRECT("‘Likviditet 2023.ods’#$‘1’.S19") from this site, and pasted it in a cell in Calc - I got the right value - from the other spreadsheet.

I saved and closed the spreedsheet (no problem)
When I open it I got the message (freely translated)

The following external file could not be opened. Data linked from this file was not updated.
and a path to another directory (but the right filename)

In the cell now it stand #REF! ,
The next time I open the same spreadsheet, the “external file could not be opened” message does not appear.
You’re right it shouldn’t work - but it did - for a while.

Installed OO and opened the same spreadsheet. - Apart from missing an instruction - I experienced no problems - the cells updated as I had hoped. (but I hadn’t intended to switch to OO - right now)

Since you seem to have an understanding of RegEx I would like to do some tests
The base is the information that the instruction
=Cell(“filename”) gives
’file:///C:/Users/ … /Likviditet 2023.ods’#$‘52’.Q19

How would REGEX be written for

1. Select the year from the file name? (in this example 2023)
   today I use this formula .:
   =VALUE(MID(CELL(“FILENAME”;A1);FIND(".";CELL(“FILENAME”;A1))-4;4))

2. Select the weeknumber from the file name? (in this example 52)
   today I use this formula .:
   =VALUE(MID(CELL(“FILENAME”);FIND("#$";CELL(“FILENAME”))+2;LEN(CELL(“FILENAME”))))

This should probably be done with several formulas
3a) With regex select the entire first part of the filename (maybe ‘(.+ods’)’ )
(In this example .: ‘file:///C:/Users/ … /Likviditet 2024.ods’ )

3b) With RegEx replace the year with the next year,
(In this example .: …Likviditet 2023.ods → …Lividitet 2024.ods)

3c) Concatenate the new file name with tab and cell (which should always be the same)
(the result could be ’file:///C:/Users/ … /Likviditet 2024.ods’#$‘1’.Q19)

Hope I didn’t miss anything.
With such a formula, I hope the updates will work from an external spreadsheet.
With these relative links, it becomes easy to add a week. eg. when the year have 53 weeks.

(Did a quick estimate and the spreadsheet contains about 36,000 formulas - it can be little slow to open with all the links etc.)

[Albireo]

Now I have created two test files with some tabs and a simple structure (these are written by the Swedish version of LO - I don’t know if it works)
TestCalc 2023.ods (21.1 KB)
TestCalc 2024.ods (21.1 KB)

A brief description of my desire is .:
Create formulas that change when a new file is created.
Formulas that adapt if the files are moved from a local hard drive to a network drive or …

Description of the different tabs and cells
Tab - Start
The formula in cell C10 retrieves a value from the year before (example)
File .: TestCalc 2022.ods - - - - Tab .: 2 - - - and - - - Cell .: D12
(this file is easy to create) But if the file is missing, some message is displayed, such as “missing” in that pink cell.

Tab - 1
The value in the Cell D13 will appear elsewhere (according to text)
Not that important, but is there a better way to handle dates and days?
I don’t know if you see the same day dates and weeks as I do?

Tab - 2
Pretty much the same as in the tab Start, but the value in Cell E22 (pink field) must come from another file, as described in the file.