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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.