Hi everyone, I can’t select similar values in a dropdown list in Calc. Let’s say I have this list of names “John Axel John Mary”, I have 2 guys named John.
When I make a dropdown list with these names, I can only select the 1st John. If I select the 2nd John, it actually selects the 1st one.
How can I select a value that is the same as another one in the list? If I just can’t, is there another solution or workaround?
Why is this needed? Give an example of a task with some data.
In lookup lists, no one identifies values by position; values must be unique.
71755.ods (13.1 KB)
The form control selects the right index into E1.
The validation list has always the first John selected when you drop it down but it makes no difference if the values are equal. If you append a space to one of the Johns, the validation box selects the right one.
Thanks for this tip, the form control seems to be the solution. Now I can’t figure out how you did that but I’ll search on the internet.
How did you notice that the selected John was the first one of the two Johns?
If you want to select from a list containing repetitions (Bad idea!), you need to dismbiguate the entries in one or another way. The FormControl @Villeroy used, identifies the chosen entry by its index, and has means to output either this index or the entry itself to the linked cell.
Using a Data>Validity setting, you need to do the disambiguation explicitly yourself. See example (based on Villeroy’s sheets).
disask71755_disambiguateSelectionFromAmbiguousList.ods (23.9 KB)
@Lupp : (If you think this is out-of-bounds let me know and I’ll start a new question.)
You concatenate right to the LookupArray for the MATCH function. I am aware that some functions are more “array aware” than others, but I didn’t realize this was possible, certainly not outside of a CSE. Can you share the logic of what parameters for what functions might allow this sort of thing?
For example,
=T(D7:D11 & ROW(D7:D11))
has to be a CSE or it returns #VALUE!.
Similarly
has to be CSE, although, for example, Documentation/How Tos/Using Arrays - Apache OpenOffice Wiki makes no distinction between SUM and MATCH.
Concerning some more basical concepts, a wiki page won’t help much probably, and in many additional cases a help page will restrict its help to the “most relevant/frequent” (as seen by the author) use-cases, and exemplify thíngs in the most primitive way. Therefore these means are often next to useless or even misleading (imo) if there are serious questions.
In addition there is a tendency to only talk about the purposes and the trouble-free working. As an old teacher I know that demonstrating expectable errors and typical cases of misuse is indispensable when trying to teach well.
In short: Help pages don’t teach, but are used as if they do (and probably are intended this way). Not teaching concepts but pretending to teach may lead to hubris instead of actual skills. The examples-only-way is even worse.
I don’t feel capable of doing better in a wide range of topics. Therefore just a few hints:
Concerning the behaviour of standard functions including some subtleties with respect to
you need to study the related part of the ODF specification. For the current spec version 1.3 this is part 4: Open Document Format for Office Applications (OpenDocument) Version 1.3. Part 4: Recalculated Formula (OpenFormula) Format (also available in pdf). Make sure to understand the “iterative” mode of array-evaluation, the meaning of the parameter specification ForceArray, the Sequence types, …
Example: The second parameter of MATCH() is ForceArray. All the allowed parameters of SUM() are Sequence types.
If you dislike this kind of studies too much, you need to avoid any higher-level usage of Calc.
Concerning UI-related toys like >Data>Validity or >Tools>AutoInput there is no specification afaik, and even to the Calc Guide (7.1 is mine) my introducing remarks concerning teaching apply.
Therefore there also can’t be reliability - and you can’t talk of bugs where the correct behaviour isn’t specified.
Just one point: The Source field of >Data>Validity in Cell range mode is behaving like a parameter position specified ForceArray as far as I understand.
And a general remark concerning data:
If you insist on using spreadsheets in specific cases as data tables, you should regard the most basic principles of databases.
Among them:
Without an unambiguous identification of a dataset by any input, you can’t associate additional data correctly.
Concerning the original example: @Villeroys suggestion is much more helpful than the alternative I gave. I mainly did it to make the cause of the failure “more clearer”. 
I test each function separately before composing an array formula. It is quite clear that SUM, AVG, MIN, MAX etc. accept arrays because they aggregate many values to one value.
A non-array function like LEN(A1) is specified to take one and return one.
If my formula contains LEN(A1:A99), I expect LEN to return 99 values which forces me into array context even if I want to get a single value in the end as in MAX(LEN(A1:A99)) in order to get the maximal string lenght in A1:A99.
Same thing with a formula containing MATCH(A1:A99 ; X1:X999) which I expect to return 99 values because I pass 99 search values instead of one.
In these cases I use to do a quick test on a blank area with LEN(A1:A99) and MATCH(A1:A99 ; X1:X999) in order to see how these function calls evaluate in array context before using them in a nested formula. I suspect that there are some functions which do not work as expected or not at all in array context. Testing single function calls in array context helps you to better understand how they actually work (or not).
Functions with Sequence parameters accept arrays, but are not eligible for array-
evaluation.Passed arrays are automatically converted to sequences without a specified order in this case.
To find more about the details concerning ForceArray, intersection-mode and the like it is necessary to study the above linked OASIS document. Personal experience may be misleading in some cases. AOO, e.g, never applied the (old) ForceArray specification for the second parameter of MATCH() correctly. That’s a bug, not a feature. AOO also didn’t shift to V 1.3 of ODF.
An additional test for sub-expressions to be used in a formula should nonetheless be a good idea. It can’t distinguish correct from bug, however. And even this thoughtful way can fail concerning the composed formula: LibO. e.g, is treating p2 of MATCH() correctly for a long time now -generally- and EXACT() is eligible for array-evaluation and works correctly insofar if applied on expression level 0…EXACT() applied to an array, but placed on p2 of MATCH() is not treated correctly, though. It’s a mess.
Thank you both for the advice. My intent was never to imply any bug or even quirk in LO or the docs, but just about when a person can expect array behavior with vs. without CSE for certain parameters. I mentioned the user documentation to imply that I knew I was asking a fringe question. Sounds like it’s basically “See if it works, but to avoid problems, make sure if it works it’s also in the specs.”
Generally the functions work as specified. The inconsistency concerning EXACT() I mentioned is the only bug of the kind I know. However, it was never reported, and I personally don’t use the function. Thus I also didn’t report the bug yet.