Referencing cells from a shared Spreadsheet pulls from wrong sheet

[Timburbur]

I have 2 shared spreadsheets I am trying to pull data from and have the data in 1 private spreadsheet. The shared Spreadsheets are on a shared Network Drive (X:).

Spreadsheet 1 has 2 Sheets (Test 1 and Test 2)
Spreadsheet 2 has 8 Sheets (Abc_1, Abc_2, Abc_3, …)

Referencing cells from Spreadsheet 1 using this formula
=‘file:///X:/Spreadsheet 1.ods’#$‘Test 1’.A1
works well and pulls correct data always.

But when using the same formula
=‘file:///X:/Spreadsheet 2.ods’#$Abc_7.B2
for some reason pulls data from Abc_2 B2 instead of Abc_7 B2.

When I create a completely new spreadsheet and use the formula the cell initially displays the correct information, however after saving, closing and reopening it once again pulls data from the wrong sheet, and always from the same Abc_2 instead of Abc_7 sheet.

Also when trying to pull data from
=‘file:///X:/Spreadsheet 2.ods’#$Abc_6.B3
it instead pulls from Abc_3 B3.

I have tried making a couple of new spreadsheets, installing the newest version of LibreOffice Calc and resetting to factory setting, but nothing helped.

Does anyone have an idea what the problem might be and what I can try to fix it?

[Villeroy]

Spreadsheet 1.ods (10.5 KB)
Spreadsheet 2.ods (31.0 KB)
What?

[schiavinatto]

I advise you to use only LETTERS and NUMBERS in spreadsheet names. I’ve had problems with that.

[Timburbur]

So in that example, imagine there is a third “Spreadsheet 3”, that is trying to pull information form both of those Spreadsheets.

Pulling from Spreadsheet 1 Test 1 A1 gives correct information
But trying to pull from Spreadsheet 2 Tabelle 6 B1 instead displays information from Tabelle 2 B1.

This is the problem I’m having.

[Timburbur]

Thank you for the advice. Unfortunately I cannot change the name of any of the Spreadsheets I am trying to pull information from.

[schiavinatto]

Were the files generated directly in Libreoffice?

[Timburbur]

I believe so, but I am not 100% sure. The Spreadsheets contain only Text and Numbers, however around 10 people have access and use both shared Spreadsheets.

[schiavinatto]

The 10 people who have access depend on information from others to feed the spreadsheets, or just one person who generally accounts for the data.

---

As 10 pessoas que têm acesso, dependem da informação dos outros para alimentar as planilhas, ou só um que contabiliza geral os dados.

[Timburbur]

Only 2 people add information to Spreadsheet 1, and generally only those 2 use actively Spreadsheet 1.
Spreadsheet 2 (the one I have problem pulling information from) is used by around 9 or 10 people, but not often, usually once or twice a day and never at the same time.

[schiavinatto]

I don’t know how many cells are being linked, have you thought about using Spreadsheet/External Links.

The useful cells in spreadsheet1 would be concentrated in a named area of the spreadsheet, and in spreadsheet2 it would link to this area and use these as a reference for internal links.

---

Não sei qual o volume de células que esta linkando, já pensou em usar Planilha/Links externos.

As celulas uteis da planilha1, concentrar em uma area nomeada da planilha., e na planilha2 faria a ligação com esta area e usaria estas, como referencia para links internos.

[Timburbur]

Thank you for the idea. While it doesn’t solve my problem completely this does help me bypass it. I will just link to the whole sheet and then take what information I need.
Thank you!