Excel AutoComplete DropDown list doesn't work on ALL entries, only some.

I had this question after viewing Create Autocomplete Drop Down List on Spreadsheet..

I tried using the SS you attached for the previous user to see if your formulas/setup works with what I am trying to do. I can attach my template to show you what I am trying to do. I had originally done this without any code as a work around and I thought that may be the issue, but it didn't seem to work when I implemented my list into your sheet either. I have a list of part numbers that is Numbers, Words, includes symbols and I need it to dropdown with the options that match according to what is typed so far. I've tried making sure I double click each time and different things to make it work but the dropdown doesn't always work.  For example if you type (into column A under Item#s) 25-005 and then hit alt + down, it should show you all parts that start with that, and it doesn't. however if I back up and type 25-00 then alt+down it will show matches for that and then if I add the 5 back on, I can get it to give me the correct list of items. Then next if you try M180 and alt+down I can not find anyway for this one to ever pop up. I've tried sorting the list different ways, coverting all to text, I'm out of ideas. Is there a limitation to Excels autocomplete options when you are using text,num and symbols in the same list?
WO-Shortage-Template--testing.xlsx
melissa tolliverAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
The solution you refer to use macro code, and your sheet has none.

Here is another method I made for something similar.
When you select a cell in the range A5:A45 (the range is named ItemValidation), a box (userform) pops up on top of the cell, after a short delay because the long list has to be loaded.
When you start typing, the first match to the characters typed is shown.
Use Enter or the Ok button, and the selected item will be inserted in the cell.
You can also use the arrow at the right and select on the list, and then Enter or Ok
Use Esc to close without inserting item.

The box pops up when a single cells is selected, so you can delete items on the list, by selecting more than one cell with the mouse, and press Delete.

I recommend you sort the list on "Master Inventory Item List" to make it easier to find the items.
The solution had automatic sort of the list when something changed in the list.
Could be added.

I have changed the ItemOnlyList to be dynamic to avoid the blanks at the bottom.
WO-Shortage-Template--testing.xlsm
1
 
Serena HsiMarketing ConsultantCommented:
I can duplicate your error and I see a few things:

In the data validation setup (go to: Data / Data Validation), the drop-down is looking for a named range called "ItemOnlyList" which, when you select onto the named range from the cell selection field (upper left above the column headings) reveals nothing.

You can see where these named ranges point to by using Control-F3 or go to Formulas / Name Manager, then select a Name to see what it refers do.

Was your attached spreadsheet part of a larger set of worksheets in the same workbook, or does the named range refer to a set of cells in a separate workbook? If it is the latter, you'll need to be in possession of and have open the other workbook with that named range (your inventory list).

You can also re-spec a named range by selecting what range it really refers to, in your workbook it would be something like:

='Shortage Sheet'!$A$46:$A$14775

At the moment, your drop-down is trying to pick up inventory values from a list it cannot see; even though you can see the values populated in the drop-down list.
0
 
melissa tolliverAuthor Commented:
Thank you this is exactly what I needed. I will play with this one a little and let you know if I have any further questions! Thanks for your time!
0
 
TracyVBA DeveloperCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: Ejgil Hedegaard (https:#a42363254)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

broomee9
Experts-Exchange Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.