Description: The attached test case shows a named cell range defined in Sheet1 (A1:A4). There is a droplist on the same sheet that works. On Sheet2, there is a droplist that refers to the named cell range and that produces an empty droplist. Also on Sheet2, there is a droplist that refers to the cell range by cell references only (not a named cell range). The latter droplist works fine. Steps to Reproduce: 1. See attached test case. Actual Results: An empty drop list is seen. Expected Results: The populated drop list should be seen. Reproducible: Always User Profile Reset: No Additional Info: Version: 6.2.0.3 Build ID: 98c6a8a1c6c7b144ce3cc729e34964b47ce25d62 CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; Locale: en-AU (en_AU.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 150250 [details] Reproducible test case
Created attachment 150256 [details] simple testcase the behaviour is reproducible, but i tend to say it is invalid cause the global named range "PHPP_Daten_Ausrichtung_Bauteil" points to cell range "Sheet1.$A$1:Sheet1.$A$4" instead of "$Sheet1.$A$1:$Sheet1.$A$4" or "$Sheet1.$A$1:$A$4" if you enter values in Sheet2.A1 - Sheet2.A4 they will appear in data validity cell Sheet2.B5
if you open the "Manage Names" dialog (Ctrl+F3) on "Sheet1" the named range will point to "Sheet1.$A$1:Sheet1.$A$4", if you do same on "Sheet2" it will point to "Sheet2.$A$1:Sheet2.$A$4". this is already reproducible with AOO 4.1.5.
Created attachment 150259 [details] simple test case created with excel 2016 created a simple test case with ms excel 2016: LO will import a *.xlxs spreadsheet with global named range: =Tabelle1!$A$1:$A$3 to $Tabelle1.$A$1:$A$3. and will export "global" named range "Sheet2.$A$1:Sheet2.$A$4" (defined on Sheet1) to "Sheet1!$A$1:$A$3" steps to reproduce: - open *.ods attachment "simple testcase" https://bugs.documentfoundation.org/attachment.cgi?id=150256 - save as *.xlsx - open with excel - Sheet2.B5 dropdown list now displays values from cell range: Sheet1!$A$1:$A$3
If it is a bit of useful information, I'm almost certain the .ods spreadsheet I am having this problem with was saved from an Excel spreadsheet.
(In reply to Oliver Brinzing from comment #3) > if you open the "Manage Names" dialog (Ctrl+F3) on "Sheet1" the named range > will point to "Sheet1.$A$1:Sheet1.$A$4", if you do same on "Sheet2" it will > point to "Sheet2.$A$1:Sheet2.$A$4". Normal behaviour because the sheet references are relative (not absolute without a leading $) (In reply to Oliver Brinzing from comment #4) > LO will import a *.xlxs spreadsheet with global named range: > =Tabelle1!$A$1:$A$3 to $Tabelle1.$A$1:$A$3. > > and will export "global" named range "Sheet2.$A$1:Sheet2.$A$4" > (defined on Sheet1) to "Sheet1!$A$1:$A$3" Both is expected, because Excel does not know relative sheet references. Nothing we can do about. Not a bug.
Is the relative vs absolute sheet references explained somewhere in the documentation? I wasn't able to find much beyond the standard cell reference material. Thanks.
OK, I understand now (after a bit of experimentation with a trivial spreadsheet).