Automatische Texterkennung mit EXCEL Dropdown

%3CLINGO-SUB%20id%3D%22lingo-sub-1610146%22%20slang%3D%22de-DE%22%3EAutomatic%20text%20recognition%20with%20EXCEL%20drop-down%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610146%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%2C%20I%20want%20to%20make%20a%20catalog%20in%20Excel%20with%20the%20Excel%20dropdown.%20However%2C%20instead%20of%20always%20having%20to%20choose%20manually%20which%20word%20to%20take%2C%20I%20would%20like%20to%20have%20the%20choices%20filtered%20automatically.%20For%20example%2C%20when%20I%20type%20in%20the%20first%20two%20letters%20of%20a%20word%2C%20I%20get%20all%20the%20words%20that%20start%20with%20the%20letters.%20Is%20that%20possible%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELg%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610217%22%20slang%3D%22en-US%22%3ERe%3A%20Automatische%20Texterkennung%20mit%20EXCEL%20Dropdown%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610217%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770163%22%20target%3D%22_blank%22%3E%40JuliusDaredevil%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20not%20possible%20with%20the%20data%20validation%20dropdown%2C%20but%20it%20can%20be%20done%20using%20a%20combo%20box.%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fwww.contextures.com%2FxlDataVal10.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EData%20Validation%20Combo%20box%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610422%22%20slang%3D%22en-US%22%3ERe%3A%20Automatische%20Texterkennung%20mit%20EXCEL%20Dropdown%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770163%22%20target%3D%22_blank%22%3E%40JuliusDaredevil%3C%2FA%3E%26nbsp%3B%20Yes%20there%20are%20a%20number%20of%20tutorials%20on%20how%20to%20create%20this%20functionality.%26nbsp%3B%20Basically%20you%20use%20a%20helper%20column%20so%20the%20data%20validation%20is%20based%20on%20this%20helper%20column%2C%20which%20is%20a%20filtered%20list%20based%20on%20what%20you%20type%20in%20that%20cell.%3C%2FP%3E%3CP%3ESo%20if%20column%20A%20has%20the%20list%20and%20cell%20C1%20has%20the%20dropdown%2C%20then%20in%20cell%20B1%20enter%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORT(FILTER(A%3AA%2CLEFT(A%3AA%2CLEN(C1))%3DC1))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20C1%20data%20validation%20list%20would%20be%20based%20on%20column%20B%20so%20after%20you%20type%20your%20first%20letter%20or%202%20then%20hit%20the%20dropdown%20you%20only%20see%20the%20entries%20in%20the%20list%20that%20match.%3C%2FP%3E%3CP%3ENOTE%3A%20I%20have%20seen%20some%20reports%20that%20Mac%20Excel%20doesn't%20update%20when%20you%20hit%20the%20dropdown%20arrow%20so%20they%20have%20to%20hit%20enter%20THEN%20dropdown%20button.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20link%20with%20a%20tutorial.%20This%20tutorial%20narrows%20the%20list%20based%20on%20a%20more%20general%20search%20(the%20typed%20info%20can%20appear%20anywhere%20in%20the%20list%20item%20instead%20of%20only%20starting%20as%20you%20requested)%20but%20the%20technique%20is%20well%20explained%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Fsearchable-drop-down-list%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xelplus.com%2Fsearchable-drop-down-list%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hallo, ich möchte einen Katalog in Excel machen mit dem Excel Dropdown. Allerdings würde ich gerne, anstatt  immer manuell aussuchen zu müssen welches Wort man nimmt, gerne die Auswahlmöglichkeiten automatisch gefiltert haben. So dass ich beispielsweise, wenn ich die ersten zwei Buchstaben eines Wortes eingebe, alle Wörter angezeigt bekomme die mit den Buchstaben anfangen. Geht das ? 

 

Danke für eure Hilfe!

 

LG 

2 Replies

@JuliusDaredevil 

That is not possible with the data validation dropdown, but it can be done using a combo box.

See Data Validation Combo box 

@JuliusDaredevil  Yes there are a number of tutorials on how to create this functionality.  Basically you use a helper column so the data validation is based on this helper column, which is a filtered list based on what you type in that cell.

So if column A has the list and cell C1 has the dropdown, then in cell B1 enter something like:

 

 

=SORT(FILTER(A:A,LEFT(A:A,LEN(C1))=C1))

 

 

then C1 data validation list would be based on column B so after you type your first letter or 2 then hit the dropdown you only see the entries in the list that match.

NOTE: I have seen some reports that Mac Excel doesn't update when you hit the dropdown arrow so they have to hit enter THEN dropdown button.

 

Here is a link with a tutorial. This tutorial narrows the list based on a more general search (the typed info can appear anywhere in the list item instead of only starting as you requested) but the technique is well explained:

https://www.xelplus.com/searchable-drop-down-list/