Bug 61743 - Wrongly Absolute Path Referencing to named Ranges Across Documents
Summary: Wrongly Absolute Path Referencing to named Ranges Across Documents
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Whiteboard: BSA
: 112224 (view as bug list)
Depends on:
Blocks: Cell-Reference
  Show dependency treegraph
Reported: 2013-03-03 16:10 UTC by Wolfgang Jäger
Modified: 2021-10-01 07:56 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

Simple test kit. (12.31 KB, application/x-zip-compressed)
2013-03-06 07:45 UTC, Rainer Bielefeld Retired
external link for an ODS with a lost slotname in the path (originally it was there) (19.90 KB, image/png)
2016-06-30 12:48 UTC, riesslibo

Note You need to log in before you can comment on or make changes to this bug.
Description Wolfgang Jäger 2013-03-03 16:10:20 UTC
Problem description: 
Referencing into another Calc document does sometimes result in partly saving a path (to a referenced file) wrongly absolute. 
The error is, of course, not visible as long as all files reside in an unchanged directory-path. Moving to another directory unveils the problem. 
NB ALL files (in my main example 3) reside in the same directory and were moved AT ONCE.
Strange is (in my eyes) that in a (somehow complicated) formula where 2 external references occur (one as range for an INDEX function call, the other one as range in a MATCH fc inside the INDEX fc) the 1st one is treated correctly, the 2nd one shows up with the absolute path of the former location. 
The same construct with similar parameters in a neighbouring cell does not cause an error. 
Each one of the concerned references uses an external named range. The named range in the two mentioned MATCH-parameter positions are THE SAME while one is treated correctly and the other one wrongly. Replacement of the range-names by direct references helps reproducible (but is not welcome). 

Steps to reproduce:
The mentioned example shows the bug reproducible.
Switching from the usage of named ranges and adressed range references and back does reproduce the (meanwhile) expected behaviour correct/wrong.
After having switched to addressed ranges I had done some reworking. Newly trying to return to the usage of named ranges resulted (partly) in errors.
I tried a bit to design a minimal example for the reproduction of the bug but did not succeed till now. 
Current behavior:

Expected behavior:
As described 
My (only) example is not really slim and not really 'open', too. It uses downloaded data that are free for use but possibly not for further proliferation. 'Cleaning up' is not really easy. 
I give two examples taken from neighbouring cells.
After moving:
WRONG from cell E4 (second external reference):
OK from cell E5:
Before moving:
From E4:
From E5:

Operating System: Windows XP
Version: release
Comment 1 Rainer Bielefeld Retired 2013-03-03 18:07:15 UTC
@reporter (In reply to comment #0):
Thank you for your report – unfortunately important information is missing.
May be hints on <http://wiki.documentfoundation.org/BugReport> will help you to find out what information will be useful to reproduce your problem? 
Please add all information requested in following:
- Write a meaningful Summary describing exactly what the problem is
- Attach a test kit with source and target sample document (not only 
  screenshot) or refer to an existing sample document in an other Bug 
  with a link; to attach a file to this 
  bug report, just click on "Add an attachment" right on this page.
- Attach screenshots with comments if you believe that that might explain the 
  problem better than a text comment. Best way is to insert your screenshots
  into a DRAW document and to add comments that explain what you want to show
  (attachment 68877 [details], attachment 68490 [details])
- Contribute a document related step by step instruction containing every 
  key press and every mouse click how to reproduce your problem 
  (similar to example in Bug 43431)
– if possible contribute an instruction how to create a sample document 
  from the scratch
- add information 
  -- what EXACTLY is unexpected
     Does it look absolute, or do you really get problems when you rename
     the folder?)
  -- and WHY do you believe it's unexpected (cite Help or Documentation!)
  -- concerning your PC 
  -- concerning your Operating System (Version, Distribution, Language)
  -- concerning your LibO version localization (UI language, Locale setting)
  –- Libo settings that might be related to your problems 
     (menu 'Tools -> Options -> Load/Save -> General - Links relative to 
     file system?)
  -- how you launch LibO and how you opened the sample document
  –- Whether your problem persists when you renamed your user profile 
     before you launch LibO (please see
  -- Whether that worked in former LibO Verions or AOOo / OOo
  -- everything else crossing your mind after you read linked texts

Please keep in mind that you should not talk about what you see but to describe  how we can reproduce the problem.

Please always give hint what seems unexpected to you. It's really too hard to examine your long links and to try to find out what might be unexpected for you.
Comment 2 Wolfgang Jäger 2013-03-04 00:39:54 UTC
### Restart ###

See also ZIP:

On my PC with
OS: Win XP Professional 2002 SP3 32 Bits Germnan locale
Processor: AMD Athlon 64 3000+ , 2.01 GHz, 1.00GiB RAM

the following steps were performed. 

Already designed was a triple of Calc-documents. 
These were part of a little project initiated by the questions of a user in the forum 'http://en.libreofficeforum.org/forum/apps/calc'.

The final document 'WochenKalender002.ods' takes over values from 'Bearbeitet_AGr_NT_MaK003.ods' by formulas and so does the second document from 'HerrnhuterLosungenFree 2013.ods'. 

LibreOffice Calc was Ver, English UI and function names. 
The options 'Save URLs relative to file system / internet' were both activated. 

1) On an USB stick drive (in my case G:) in the first level directory 'OtherDocs'
the tree (German locale)
Auflistung der Ordnerpfade für Volume TOSHIBA
Volumenummer: ...
|   | 
|   +---1st Other Path Step 1 Addressed
|   |   \---NewWorkingFolder
|   | 
|   +---AbsoluteInsteadOfRelative Named
|   | 
|   \---2nd Other Path Step 1 Named
|       \---NewWorkingFolder
|       ...

was created. 
2) From a folder on my drive 'D:' where it was developed the triple of linked files of which 
the project consists was packed into a .zip (with WinZIP 9.0) named 'KalenderTripel20130302CalendarByAddressedRanges.zip'. 
The 'WochenKalender002.ods' contained in this zip file was finally put in a version avoiding named ranges in certain formulas because some days ago a bug had occurred if named ranges were used.
3) The zip file was copied to the folders 
'G:\OtherDocs\CalcBugs\AbsoluteInsteadOfRelative Named' and 'G:\OtherDocs\CalcBugs\1st Other Path Step 1 Addressed\NewWorkingFolder' . 
4) In the first one of these folders the files were extracte from the zip,
opened in the order 'Herrn...', 'Bearb...' and 'Wochen...' with updating the links and the correct function was tested.
5) The formulas in the cells E4 and E5 of the sheet 'DruckWochenblatt' of the document 'WochenKalender002' were now edited changing a total of 4 parts (2 in each formula):
Replace the addressed range '$ZuordSchluessel.$AL$11:$AP$8010
by the named range 'nrAGrVerse' !
Replace the addressed range '$ZuordSchluessel.$K$11:$K$8010
by the named range 'nrKalenderTage' (occurs in both formulas)!
Replace the addressed range '$ZuordSchluessel.$L$11:$L$8010
by the named range 'nrStellen' !
Each of these ranges occurs exclusively following the '#' as target inside the file 
'Bearbeitet_AGr_NT_MaK003.ods' existing in its then directory path described as
'file:///G:/OtherDocs/CalcBugs/AbsoluteInsteadOfRelative Named' .
Inside the file the addressed ranges and the named ranges respectively denote exactly the same references.
6) The edited document was saved under unchanged name.
7) The two unchanged documents and the edited one were now zipped into 
'KalenderTripel20130302CalendarByNamedRanges.zip' and this file was copied to 
'G:\OtherDocs\CalcBugs\2nd Other Path Step 1 Named\NewWorkingFolder' .
8) The 'Addressed' version was unzipped and tested in its NewWorkingFolder.
All Files and LibreOffice were closed again.
9) Same procedure performed with the 'Named' version in its dedicated folder.
10) The results were as formerly described: 
In the cell E4 the expression could not be evaluated because the MATCH part contained the now wrong absolute path to the former location where the file did no longer exist. 
11) NotaBene: EXACTLY the same MATCH part occurred in E5. There the path was correctly saved in relative mode.
12) Imagine this file existed still but was for some purpose changed!
13) It's really a problem of reliability, too.

Finally the (malfunctioning) version of the project (using named ranges) was also tested with Portable LibreOffice Ver., German UI and function names. 

Same result.

All files closed
LibreOffice closed
Unzipped files deleted
Tree containing the zip files and the LibO Writer document containing this text zipped into 'LibO_Calc_BugReport_AbsoluteInsteadOfRelativePathsIfRangesNamed.zip'

This text added to new comment to Bug Report #61743
Zip file attached
comment posted

Thank you for caring!

Of course I might be as well more concise and more precise if writing about this in German.

### Another Kind Of Reply ###

Dear expert!

Thank you very much for your quick reaction. 
I really appreciate that there are experts fighting bugs. 

In this case I experienced a bug that I find annoying though it occurred only during work on one "project" - and this not being one that might get critical for me but one that I undertook to help a guy who asked a question in the LibreOfffice Calc forum. 

Unfortunately I did obviously not succeed in describing the problem precisely. 

You complained my report being not helpful enough.

> Thank you for your report – unfortunately important information is missing.
> May be hints on <http://wiki.documentfoundation.org/BugReport> will help you 
> to find out what information will be useful to reproduce your problem? 

I tried understanding and regarding that. 

> Please add all information requested in following:
> - Write a meaningful Summary describing exactly what the problem is

I did as well I can. At least I thought so. 

My only example so far is a triple of documents and the bug shows in connection with MOVING these documents to another location in a directory tree - (or to another file system or computer).  So it is really not easy to deliver a simple example. Anticipating your reply I already tried to design a minimal example - but did not succeed. I told all this in the original report.  

OK! Let's try another approach. Please be aware of my not being an expert. Terminology as well as interpretations may be erroneous without my knowing it. My poor English may be another problem.
Comment 3 Rainer Bielefeld Retired 2013-03-04 06:14:09 UTC
I gambled a little, fond an error message in 
"..\2nd Other Path Step 1 Named\NewWorkingFolder\WochenKalender002.ods", of cousre in Sheet DruckWochenblatt Cell E5 I see the reference to drive G:, but no idea what that should tell me, ...
May be someone else is willing to try to find out what the problem is? I will not do before we have a description for what less than 1 wee holidays is needed to understand.

There seems some misunderstanding. We do not need lots of information, we need relevant information. There is no need to run a complete universe simulation to show what happens if you turn a water glass. Simply put the glass on a table and turn it.
As long as your report does not look like following (and is not much longer) you are doing something wrong. Most important: Do not tell what you did, tell what we have to do to reproduce the problem with your test kit.

--- Example ----
1. Unzip testkit, copy folder "testkit" into root folder c:\
2. Open document C:\testkit\xyz.ods
3. Check Cell A3, should show value "12345", linked from document in 
   C:\testkit\subfolder\uvw.ods,  Cell A3
4. Close all documents
5. rename folder "testkit" to "testkit1"
6. redo analogous from step 2
   Expected: In step 3 cell A3 still shows "12345"
   Actual: A3 shows message "Error:xyz"

How to create problem-links:
11. In  C:\testkit\subfolder1\uvw.ods, Cell A4:
12. do this
13. do that
14. and so on
15. close all documents
16. rename folder "testkit1" to "testkit2"
17. Open, check,
Comment 4 Wolfgang Jäger 2013-03-04 11:57:02 UTC
@ Rainer Bielefeld
We should avoid misunderstandings about our attitude and our intentions. 
Therefore I retire (temporarily) from commenting this thread. 
I sent a PN.
Comment 5 Rainer Bielefeld Retired 2013-03-05 21:34:20 UTC
I got additional detailed and structured info how to reproduce the problem from Wolfgang, so that I now am able to reproduce his observations. It's easy, but currently I only see the effect without any ideas concerning the roots. I should be able to contribute a much simplified test kit and step by step instruction tomorrow. With that kit it will be easy to do more tests (OS? Started with Version?, ...)
So I assign Bug to myself for this work.
Comment 6 Rainer Bielefeld Retired 2013-03-06 07:45:59 UTC
Created attachment 75999 [details]
Simple test kit.

Here as announced a more simple test.

Steps how to reproduce with  "LibreOffice " German UI/ German Locale [Build-ID: 5b93205] {pull date 2013-01-18} on German WIN7 Home Premium (64bit) 
1. Unzip Testkit to (sub-)folder "SimpleTest" as suggested by unzipper.
   It's not necessarily relevant, but will ease test if you unzip to 
   a folder with a short path structure like D:\Test\
2. Open "Target.ods", confirm "update external links"
   Shows 2 times the same value "15" in A1, A2
3. In Cell A2 nearby end of link replace string "$Tabelle1.A2:A6" by string
   > A2 Still shows value "15"
4. Save as "Target1.ods", close
5. To check whether everything works fine reopen "Target.ods", confirm 
   "update external links"
   > Shows 2 times the same value "15" in A1, A2
6. Close without saving
7. Rename folder "SimpleTest" to "xyz"
8. To check whether everything works fine open "Target1.ods", confirm 
   "update external links"
   Expected: Shows 2 times the same value "15" in A1, A2
   Actual: Error Like "#NAME" shown in A2 Instead of having changed 
           "SimpleTest" to "xyz" (as in A1), In A2 you still find the old 
           folder name  "SimpleTest" in reference

That shows that reference has become absolute in step 3. For a workaround test you can replace "SimpleTest" by "xyz" in link in cell A2, that will heal the problem.

Alternatively you can do a "Little-Edit-Trick": In reference with wrong "SimpleTest" folder name delete the trailing "e" of range name "Sourcerange", type again immediately, <Enter>! TaDaaa, "SimpleTest" hs been replaced by "xyz"

Additional information:
Where did the problem start?
OOo 3.1.1 and AOOo 3.4.1 can not handle reference to range name, Error "#NAME?" in step 3

LibO 3.3.3 until manages step 3, but error "#NAME?" in step 5 (what can be healed by doing a little edit in the link for example delete and retype last character of "Sourcerange". Renaming folder shows same "Absolute-Reference-problem" as per steps 7,8. In step 8 the "TaDaa-Little-Edit-Trick" (see above) will not work.

"Version (Build ID: 61add5c77de1ff963b839020c77f67f14ef07de) TinderBox: Win-x86@6, Branch:master, Pull Time:  2013-03-05_00:20:08" ENGLISH UI / German Locale  on German WIN7 Home Premium (64bit) with LODev/4 Masters User Profile: will fail in step 8, but keeps old values instead of replacing them by error message.

Long path structures with blanks in names: here I see with 3.6.5 and later the same problems as above with LibO 3.3.3 until after reopen (without having changed the path name) "#NAME?", workaround-healed by little edit in path name,  Renaming folder shows same "Absolute-Reference-problem" as per steps 7,8. 

Even more information:
a) During my test the "little edit trick" became necessary for step 5 also with LibO 3.6.5. and later. Until now I did not understand that.

b) My step by step instruction also can be used for reporter's sample document. After Unzip only "...\CalcBugs\1st Other Path Step 1 Addressed\"contents will be used. To avoid unclear side effects I recommend to rename folders to folders without blanks.

To switch to range names in "WochenKalender002.ods" in sheet 'DruckWochenblatt' cells E4, E5
+ Simply use 'Find and Replace - in Formulas - Only Selection' to
  <Replace All>
  "$ZuordSchluessel.$AL$11:$AP$8010" by "nrAGrVerse" 
     (renew selection afterwards!)
  "$ZuordSchluessel.$K$11:$K$8010" by "nrKalenderTage"
  "$ZuordSchluessel.$L$11:$L$8010" by "nrStellen"
Analogous tests to my step by step instruction for my simple test brought similar results. but I also observed something strange. I step 8 I saw the old wrong path name in the formula name, but the "little edit trick" healed even the wrong old pathname and changed it to the new one.
Comment 7 Rainer Bielefeld Retired 2013-03-06 07:52:27 UTC
@Spreadsheet Team:
Comment 6 Step by step instruction shows how to reproduce easily.
Of course some additional tests (what's with different OS, strange side effects  and so on) can be done, but may be with your code knowledge you can do most of that more efficient? Please tell us if you still need more results normal users can contribute!

Please change  Status to ASSIGNED and add yourself to "Assigned To" if you accept this Bug or forward the Bug if it's not your turf (and remove others in team from CC).
Comment 8 QA Administrators 2015-02-19 15:42:28 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2016-02-21 08:37:22 UTC Comment hidden (obsolete)
Comment 10 riesslibo 2016-06-30 12:47:26 UTC

I observed this behaviour similar to the described one here with 

Build-ID: f99d75f39f1c57ebdd7ffc5f42867c12031db97a
CPU-Threads: 8; BS-Version: Windows 6.1; UI-Render: Standard; 
Gebietsschema: de-DE (de_DE)

I moved 2 ods at the bottom of the file system c:, 
one of it with a link to a named range from one to the other.
Opening this one after aksing to refreshing the data I organized
manualy the external link with the new link destination, in words c:

Data was correctly updated, even the chart uppon it.

Closing both, changing some data in the source file, saved, opening 
the other linking to it, it asks me if in it should update the data,
answering with yes it gives a error message not possible, file not found.

Looking up manualy for the link to the named range of the source file
in the path there is the c: missing, just file:\\\<sourcefilename.ods>
see screenshot.

This is a realy strange thing, and I think an important one, there are
several other issues belonging to external links. if new ods example files
are needed give me a hint, I just not want to overdue this description,
as the original reporter gives excelent information and examples.

Thanks for overseeing this,
Comment 11 riesslibo 2016-06-30 12:48:30 UTC
Created attachment 126004 [details]
external link for an ODS with a lost slotname in the path (originally it was there)
Comment 12 Eike Rathke 2017-04-19 11:03:47 UTC
Problem seems to be that named expressions/ranges of external documents aren't saved in the external references cache of the referring document. Thus when reloading the document after the path was changed attempting to compile such a formula the name isn't found and the formula results in error, which then displays the original formula including the old path instead of an adapted one because the document path and the range name at that stage are one entity that results in the bad name token.
Comment 13 Buovjaga 2017-09-09 18:43:51 UTC
*** Bug 112224 has been marked as a duplicate of this bug. ***
Comment 14 QA Administrators 2018-09-10 02:36:50 UTC Comment hidden (obsolete)
Comment 15 ferre-jacques 2018-09-30 20:00:27 UTC
Same problem with 
Version: (x64)
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
Threads CPU : 4; OS : Windows 6.1; UI Render : par défaut; 
Locale : fr-FR (fr_FR); Calc: group

One file with 3 cells named CELL1, CELL2 and CELL3.
One file with :
- 3 cells with links as :
='file:///C:/Users/Jacques/Documents/Test1.ods'#CELL1 (or CELL2 or CELL3)
- 3 cells with links as :
='file:///C:/Users/Jacques/Documents/Test1.ods'#$Feuille1.A1 (or B1 or C1)

Links with named cells are broken when I open the second file, links with cells addresses are OK.

Comment 16 QA Administrators 2019-10-01 03:02:00 UTC Comment hidden (obsolete)
Comment 17 QA Administrators 2021-10-01 03:51:30 UTC Comment hidden (obsolete)
Comment 18 Rainer Bielefeld Retired 2021-10-01 07:56:52 UTC
Following STR 1-5 in Comment 3 I did not see anything unexpected with Version: (x64) / LibreOffice Community
Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5
CPU threads: 12; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded