Calc from file 01.xls to 02.xls, if open 02.xls, get `#REF!`

English
#1

Any idea how to apply ctrl+shift+F9 to multiple opened files (or something similar)?

Trying to migrate from Excel to Calc.
Have many files, like 01.xls, 02.xls, 03.xls…
In file 02.xls is link to 01.xls.
Link looks like this ='file:///C:/Dokumenti/AndSoOn/01_17.xls'#$V.F1619
If at first i open 01.xls, then 02.xls, all is ok (links updates).
But if 01.xls is closed and i open 02.xls, then instead of value i see #REF!.
How to update links to closed files? Do i need to rename file etc.?

Seems, yes… tried to File - Save a Copy and saved as .ods and links updates automatically without #REF!.

But the next problem. For example, i open files 01.ods, 02.ods, and 03.ods. And in 03.ods is link to 02.ods and in 02.ods is link to 01.ods. I change data in 01.ods, but in 03.ods (and even in 02.ods) data does not updates (i see old, incorrect data). I can ctrl+shift+F9 in each file… but if i have for example 10 files and i frequently change data…
Any idea how to apply ctrl+shift+F9 to all opened files (or something similar)?

#2

Try to embed the contents of the spreadsheet documents into some sheets of one document. The LO can handle many sheets. Then you will not need external links.
Are these documents huge sized with millions of data??

#3
  1. Can you attach two small 01.xls and 02.xls as examples showing the issue for you? (With a few examples I have at hand I didn’t get that error.
  2. What’s your setting under
    >Tools>LibreOffice Calc>General>>Update links when opening ?
#4

@Zizi64
Not huge number of rows.
But i trying to use for accounting. For example, i record transactions for January, 2023. In one sheet i record, in another sheet i want to see totals, in the third sheet i need data for VAT return etc. Would be necessary one file for each month.

@Lupp
Under >Tools>LibreOffice Calc>General>>Update links when opening see Always (from trusted locations). On open all ok. But need updating also while files are opened and i change something in on of the opened files. Is it very complicated/time consuming to modify source code? Never tried for LibreOffice, but have php, js` knowledge. If would know where to modify, then would try…

Tried to upload some files. Idea is following. For example, i have opened all those files. I change some amount in 01.ods and expecting update totals also in 03.ods and 03.ods
03.ods (11.2 KB)
01.ods (10.0 KB)
02.ods (11.2 KB)

#7

Is that directory set as a trusted location?
(The dialog showing that option has a button linking to related help.)

Concerning the updating of external links I prefer the option 'On request'.

Forget it.

External links are a mess. MS may encourage such proceeding.
You should try to avoid it.

#8

Yes, C:/Dokumenti/AndSoOn/ is trusted location. On file opening all ok, links update.

Is it possible to create some macro? For example, on ctrl+S or other combination macros updates links of all opened files? Will try to find such solution. Seems no other suitable solution…

#9

I’d try to avoid having 12 files per year…
.
You could create a simple database with one table, maybe even dbase would be sufficient (table could be directly written from Calc). A query could then get the data of the month of interest to your sheet1 and the other sheets would update according to formula. If you think it is necessary you could even “archive/save” the version/data you are working on per month (as long as you update manually, not on every load).

1 Like
#10

@Wanderer This is very interesting. Do you mean i can type something in Calc and automatically or on ctrl+S (etc. combination) record in database? For example i copy-paste (or type) multiple rows and in certain columns, ctrl+S and these certain columns and rows record in database? And if i update/change Calc, then update also corresponding data in database?
If it is possible, then would be very good solution…

Actually not sure. Some time before i watched youtube video, where one man showed how to use Calc to insert in database. But there was possible to insert only one row. I mean type data in multiple input fields in Calc, click button and insert in database only one row. To insert next row, again must type in multiple input fields…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.