Why is this NOT possible???? I need an explanation

[iheartdata]

I built a great workbook in Excel and this feature was just "natural" and with OO I've been looking for hours to NO avail. At this point, I am just ultra interested in knowing why such a seemingly simple thing is NOT POSSIBLE in OO.

I have a lookup list on Sheet 2. I have several columns on Sheet 1 that populate with those values. The one column that I populate firstly on Sheet 1 (which is the reference for the lookups) is/should be a "Validity List" (in Excel, the Validation List). In Excel, when I start typing, the dropdown list starts narrowing down options based on letters typed and I am free to choose the Item that I want in that cell. AND the cells with the lookups populate as well. WHY IS THIS NOT POSSIBLE IN OO. THERE ARE HUNDREDS OF QUERIES ON THE "INTERWEB" ASKING THIS SAME QUESTION AND HUNDREDS OF AMAZING GURUS EXPLAINING THAT IT IS NOT POSSIBLE IN OO. WHY???????????????????

And I've hunted around to see if one of the true genuises in the world have at least added it to LibreOffice and apparently it is a "no-go" there too.

Please rest my brain with an answer.

I no longer have access to Excel or I would be well on my to a thing of beauty.

Thanks for hopefully providing me some Peace

[RoryOF]

If one needed to do this (I don't) it could probably be done as an extension, using the API functions of Calc. As you seem to require it, it will make an interesting project for you.

[Zizi64]

I have a lookup list on Sheet 2. I have several columns on Sheet 1 that populate with those values. The one column that I populate firstly on Sheet 1 (which is the reference for the lookups) is/should be a "Validity List" (in Excel, the Validation List). In Excel, when I start typing, the dropdown list starts narrowing down options based on letters typed and I am free to choose the Item that I want in that cell. AND the cells with the lookups populate as well. WHY IS THIS NOT POSSIBLE IN OO.

Please upload your ODF type sample file here.

built a great workbook in Excel

Always use the native, Intrernational Standard ODF fileformats with the AOO and LO, bevcause there is not (never was and never will be 100% compatibility between the different fileformats. You will lost some formatting properties and some functionality when you convert the file int oan another file type. And the OPENING procedure of the AOO/LO always will convert an .xls file to the native format!!! (Maybe you will save it into the original (foreign) file type: that means an another conversion!!! )

[Villeroy]

Of course you can combine a validation list with a lookup formula.
Apart from that, Libre/OpenOffice comes with a database component where all these tricks become obsolete.

[keme]

There is an option to sort validation list ascending, which would scroll the list as you type, but of course this only works when you type the start of the entry.

There is also Tools - Cell content - Autoinput which will suggest a previous value. Press tab key to see other matching previous values. This only works for values already entered in the same column.

The "data entry autofilter" you describe must be something else. How did you enable that in Excel? I made a small effort: activated validation from a cell range on a few cells, and made a few lookup formulas. I can't see anything like what you described, running Excel 16.11.1 (O365 based install) on OS-X 10.13.4 (High Sierra). I haven't needed it either (one of the features I dislike the most is the autoinput, which is enabled by default in Calc), so that's about the effort I will put into second-guessing your design.

As for the question you stated that you "need an explanation" for:
The answer may be connected to another question: Why don't you have access to Excel anymore? System requirement is 10.10 (Yosemite), so it should run nicely on 10.11 (El Capitan).

Your question:
I am just ultra interested in knowing why such a seemingly simple thing is NOT POSSIBLE in OO.

I have a lookup list on Sheet 2. I have several columns on Sheet 1 that populate with those values. The one column that I populate firstly on Sheet 1 (which is the reference for the lookups) is/should be a "Validity List" (in Excel, the Validation List). In Excel, when I start typing, the dropdown list starts narrowing down options based on letters typed and I am free to choose the Item that I want in that cell. AND the cells with the lookups populate as well. WHY IS THIS NOT POSSIBLE IN OO. THERE ARE HUNDREDS OF QUERIES ON THE "INTERWEB" ASKING THIS SAME QUESTION AND HUNDREDS OF AMAZING GURUS EXPLAINING THAT IT IS NOT POSSIBLE IN OO. WHY???????????????????

My question:
Why don't you have access to Excel anymore?

So, how does your question connect to mine? Can you make the connection?

Edit: Anyone else who can solve this riddle?