Bug 113535 - Referenced validity lists do not contain all entries
Summary: Referenced validity lists do not contain all entries
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2017-10-30 20:45 UTC by sworddragon2
Modified: 2018-05-15 11:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file (6.95 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-11-10 13:57 UTC, Buovjaga
Details
The reworked attachment mentioned in #3 . (9.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-05-15 09:30 UTC, Wolfgang Jäger
Details

Note You need to log in before you can comment on or make changes to this bug.
Description sworddragon2 2017-10-30 20:45:58 UTC
Description:
 

Steps to Reproduce:
1. Open Calc (the cell A1 should be selected by default - otherwise select it).
2. Go to the menubar -> Data -> Validity... -> Criteria and select for "Allow:" the value "List" and enter in "Entries" the 2 lines "Test1" and "Test2" and click on OK.
3. Select cell A2.
4. Go to the menubar -> Data -> Validity... -> Criteria and select for "Allow:" the value "Cell range" and enter in "Source" the value "A1" and click on OK.
5. Click on the dropdown arrow of cell A2.

Actual Results:  
The selection list is empty.

Expected Results:
The seletion list should contain the values "Test1 and "Test2".


Reproducible: Always


User Profile Reset: No



Additional Info:
In other cases the validity list might not always be empty (for example in an own document I noticed it was very incomplete instead).


User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 Buovjaga 2017-11-10 13:57:45 UTC
Created attachment 137664 [details]
Example file

Not sure, if it is required to always create it from scratch, but here is an example file to confirm the issue. Created with 6.0.

Arch Linux 64-bit, KDE Plasma 5
Version: 6.0.0.0.alpha1+
Build ID: 1aba1955f161cc112dab80b6b3e78ec7761616fc
CPU threads: 8; OS: Linux 4.13; UI render: default; VCL: kde4; 
Locale: fi-FI (fi_FI.UTF-8); Calc: group
Built on November 10th 2017

Arch Linux 64-bit
LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4
Comment 2 Buovjaga 2017-11-10 14:00:11 UTC
I also tried from scratch with 3.3 and got the same result.
Comment 3 Wolfgang Jäger 2018-05-15 09:30:38 UTC
Created attachment 142109 [details]
The reworked attachment mentioned in #3 .

The behaviour sworddragon2@aol.com describes is the expexted one. 
The 'Source' entered using 'Cell Range' mode needs to ba a cell range actually containing the accepted values or an expression returning a respective reference or array. A1 does neither contain the two entries. As soon as ther is entered one of the accepted values, the validity dropdown of A2 will offer this single entry.

How to use the 'Cell Range' mode of 'Data' > 'Validity' correctly is demonstrated in the new attachment. 

Suggest to change to RESOLVED NOTABUG.
Comment 4 Buovjaga 2018-05-15 09:36:35 UTC
Thanks, closing.
Comment 5 sworddragon2 2018-05-15 10:52:55 UTC
(In reply to Wolfgang Jäger from comment #3)
> The 'Source' entered using 'Cell Range' mode needs to ba a cell range
> actually containing the accepted values or an expression returning a
> respective reference or array. A1 does neither contain the two entries. As
> soon as ther is entered one of the accepted values, the validity dropdown of
> A2 will offer this single entry.

My thought as I created this ticket was probably that the source A1 as cell range would access the validity list from the cell A1 to fetch the values that would then be listed in the dropdown field of A2. This would be useful if multiple cells would use the same selection of entries for their dropdown field as the first cell could act as a master cell containing the raw validity list while other cells would use the master cell's validity list as reference instead of using their own same values for storage/performance reasons.

I could workaround that by writing all valid entries in cell(s) and use them as reference - but this is an ugly solution.

But maybe LibreOffice supports what I actually want to try to achieve and I just did not encounter this feature yet. If not what I'm actually looking for could be described as 'Supporting "Allow: Validity list" in the menubar -> Data -> Validity... -> Criteria' which takes as "Source" a cell pointing in any way to a validity list.
Comment 6 Wolfgang Jäger 2018-05-15 11:54:38 UTC
(In reply to sworddragon2 from comment #5)
> (In reply to Wolfgang Jäger from comment #3)
> > The 'Source' entered using 'Cell Range' mode needs to ba a cell range
> > actually containing the accepted values or an expression returning a
> > respective reference or array. A1 does neither contain the two entries. As
> > soon as ther is entered one of the accepted values, the validity dropdown of
> > A2 will offer this single entry.
> 
> My thought as I created this ticket was probably that the source A1 as cell
> range would access the validity list from the cell A1 to fetch the values
> that would then be listed in the dropdown field of A2. This would be useful
> if multiple cells would use the same selection of entries for their dropdown
> field as the first cell could act as a master cell containing the raw
> validity list while other cells would use the master cell's validity list as
> reference instead of using their own same values for storage/performance
> reasons.

I was aware of what you hoped to get. However, it is neither the actual nor the generally expected behaviour.
 
> I could workaround that by writing all valid entries in cell(s) and use them
> as reference - but this is an ugly solution.

I wouldn't judge this to be "ugly" in a sense people would generally agree with.
On the other hand the behaviour you preferred would make it impossiple to select some entries from elsewhere or from lists in a range of cells based on 'Validity' and then to use that range with the selected values in a secondary application of 'Validity' to a different cell. Thus I would judge this is more a matter of consistence than of aesthetics. (Consistence, however, has also its role in aesthetics as I see it.)  
In fact I feel it a breach of "ClarityOfData" to offer the internal 'List' option at all. What can be selected is data, isn't it? And data should be given in cells in spreadsheets. Using 'Validity' or cell attributes to represent data can easily cause problems.

> But maybe LibreOffice supports what I actually want to try to achieve and I
> just did not encounter this feature yet. If not what I'm actually looking
> for could be described as 'Supporting "Allow: Validity list" in the menubar
> -> Data -> Validity... -> Criteria' which takes as "Source" a cell pointing
> in any way to a validity list. 

Well, thats an indirection I would not miss. 

If you URGENTLY miss it, you can use the following CustomFunction under the 'Cell Range' mode in the range bar to apply the validity List from a different cell. No guarantee of any kind! (pZ is the number of the source sheet, 1-based!)

Function getValidityList(pZ As Long, pCellName As String) ' ONLY for LIST!
    Dim h : getValidityList = h ' Clear if BASIC didn't it correctly. 
pCell = ThisComponent.Sheets(pZ - 1).GetCellRangeByName(pCellName)
f1 = pCell.Validation.Formula1
    IF NOT (Left(f1, 1)="""") Then Exit Function
h = Split(f1, """;""")
u = Ubound(h)
h(0) = Mid(h(0), 2, Len(h(0)) - 1)
h(u) = Mid(h(u), 1, Len(h(u)) - 1)
getValidityList = h
End Function