drop down list and autofill

%3CLINGO-SUB%20id%3D%22lingo-sub-1942492%22%20slang%3D%22en-US%22%3Edrop%20down%20list%20and%20autofill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1942492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20workbook%20with%20a%20drop%20down%20list%20in%20column%20F%20that%20contains%2025%20or%20so%20of%20my%20most%20common%20location%20names.%26nbsp%3B%20I%20would%20like%20to%20be%20able%20to%20continue%20to%20use%20the%20drop%20down%20but%20also%20be%20able%20to%20autofill%20the%20cells%20if%20I%20type%20a%20location%20name%20similar%20to%20a%20name%20within%20a%20data%20range%20from%20another%20sheet.%26nbsp%3B%20Is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologize%20if%20I%20am%20putting%20this%20question%20in%20the%20wrong%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1942492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1943257%22%20slang%3D%22en-US%22%3ERe%3A%20drop%20down%20list%20and%20autofill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1943257%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F886290%22%20target%3D%22_blank%22%3E%40bsimpson1610%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20use%20Combo%20Box%3CSPAN%3E.%3C%2FSPAN%3Einstead%20of%20a%20Drop%20Down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EFrom%20%3CSTRONG%3EDeveloper%3C%2FSTRONG%3E%20TAB%2C%20hit%20%3CSTRONG%3EDesign%3C%2FSTRONG%3E.%3C%2FLI%3E%3CLI%3ELeft%20to%20it%20is%20%3CSTRONG%3EInsert.%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3EFrom%20the%20menu%20find%20%3CSTRONG%3EActive%20X%20controls.%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3ESelect%20%26amp%3B%20draw%3CSTRONG%3E%20Combo%20Box%3C%2FSTRONG%3E%20on%20sheet.%3C%2FLI%3E%3CLI%3ESelect%20the%26nbsp%3BCombo%20Box%20%26amp%3B%20%3CSTRONG%3ERight%20Click%3C%2FSTRONG%3E.%3C%2FLI%3E%3CLI%3ENow%20from%20the%20menu%20find%20%3CSTRONG%3ELinked%20Cells%3C%2FSTRONG%3E%2C%20assign%20the%20data%20range%20where%20you%20want%20to%20put%20selected%20locations.%3C%2FLI%3E%3CLI%3ENext%20find%20%3CSTRONG%3EList%20Fill%20Range%3C%2FSTRONG%3E%2C%20assign%20data%20range%20has%20list%20of%20locations.%3C%2FLI%3E%3CLI%3EThen%20find%20%3CSTRONG%3EMath%20Entry%3C%2FSTRONG%3E%20%26amp%3B%20select%20%3CSTRONG%3E0-frmMatchEntryFirstletter.%3C%2FSTRONG%3E%3C%2FLI%3E%3CLI%3ENow%20turn%20off%20%3CSTRONG%3EDesign%3C%2FSTRONG%3E%20mode.%3C%2FLI%3E%3C%2FOL%3E%3CP%3ENow%20hit%26nbsp%3BCombo%20Box%2C%20list%20of%20locations%20will%20appear%2C%20type%20the%20first%20letter%20of%20location.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1945452%22%20slang%3D%22en-US%22%3ERe%3A%20drop%20down%20list%20and%20autofill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1945452%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20response.%26nbsp%3B%20Unfortunately%2C%20I%20need%20to%20keep%20my%20existing%20drop%20down%20box%20for%20my%20most%20common%2025%20locations%2C%20but%20I%20would%20like%20to%20autofill%20also.%26nbsp%3B%20The%20match%20first%20letter%20entry%20would%20be%20a%20big%20help.%26nbsp%3B%20The%20reason%20I%20need%20the%20autofill%20is%20so%20other%20formulas%20in%20my%20workbook%20will%20function%20based%20on%20an%20exact%20match%20to%20the%20location%20name.%26nbsp%3B%20There%20is%20too%20much%20margin%20for%20error%20when%20several%20people%20are%20typing%20in%20their%20version%20of%20a%20location%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20options%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have an excel workbook with a drop down list in column F that contains 25 or so of my most common location names.  I would like to be able to continue to use the drop down but also be able to autofill the cells if I type a location name similar to a name within a data range from another sheet.  Is this possible?

 

I apologize if I am putting this question in the wrong place.

 

TIA

3 Replies

@bsimpson1610 

 

You need to use Combo Box.instead of a Drop Down.

 

How it works:

  1. From Developer TAB, hit Design.
  2. Left to it is Insert.
  3. From the menu find Active X controls.
  4. Select & draw Combo Box on sheet.
  5. Select the Combo Box & Right Click.
  6. Now from the menu find Linked Cells, assign the data range where you want to put selected locations.
  7. Next find List Fill Range, assign data range has list of locations.
  8. Then find Math Entry & select 0-frmMatchEntryFirstletter.
  9. Now turn off Design mode.

Now hit Combo Box, list of locations will appear, type the first letter of location.

@Rajesh-S 

Thanks for your response.  Unfortunately, I need to keep my existing drop down box for my most common 25 locations, but I would like to autofill also.  The match first letter entry would be a big help.  The reason I need the autofill is so other formulas in my workbook will function based on an exact match to the location name.  There is too much margin for error when several people are typing in their version of a location name.

 

Any other options? 

@bsimpson1610 

Other than what I've suggested, is Excel VBA Macro. Meanwhile you may check this little  back I've solved Autocomplete Excel Drop down list . Or  you may Check this also, is bit tedious!

Every where you find that the Combo box is the ultimate choice.