Searchable Drop Down List

[mhkeim]

I'm rebuilding our asset management from the ground up and then handing the actual process over to another person. Part of this process is assigning assets to a user at a certain location.

On my Excel sheet I have a column that is about 130 locations long, too much to scroll through for each asset. I'd like to have an option to click on that particular cell that the Location will be in and start typing parts of the name we know are in the location. For instance, I have 12 different Fire Stations listed. So if I start typing "Fire" it'll find and list anything with Fire in the name.

Now, I found a video https://www.youtube.com/watch?v=srTteYoqcJs&t= [Moderator added live link --6SJ]

It does EXACTLY what I want which is amazing. However, at the 23:30 mark I hit a snag. I am unable to add my ListFillRange like the guy did in the video. When I hit enter the field just clears out.

To further iterate my requirements. My list will be on the 2nd sheet of the workbook. The actual building of all the asset info will be on the first page. This will then be saved as a .csv and uploaded once it is complete. So I'll need to figure out how to have the ComboBox on the first sheet once I can get the ListFillRange working. As of now though, I've gotten to page 6 on Google Search results and so far I'm coming up empty handed as far as fix actions go.

I have TWO columns that will actually have this feature, Site and Location. The data is the same just rearranged, but it was a necessary evil to keep it that way. Right now I'm just trying to figure it out for Column K on the first sheet.

[6StringJazzer]

You made two errors setting up the formula for Locations

Please Login or Register  to view this content.

The red part is invalid--delete it. The blue part should be 136 instead of 2. When you correct those, go back and enter Locations for ListFillRange and it should all work.

Please Login or Register  to view this content.

[mhkeim]

That worked! Thank you for that. I probably clicked on a cell or something when filling that out. I do have further issue with this though after getting that portion to work.

From the list of locations. If I search for something arbitrary like "02" it will find me all Locations with that in the name. That's great. However if I search for something like "Police" it will only show the very first item that STARTS with Police.

I triple checked the formulas in Column B, C, and D to the video and they are correct. It looks like it falls apart in Column B. I did a search for Police and Column B only returns the very first finding and then that continues down to the drop down list.

Do I need to put this issue into a new thread since it's technically a new issue or is here fine as it's a continuation of the original goal?

[6StringJazzer]

Leave it here for now since it's so closely related to your original question. I'll look at it myself as time permits.

[mhkeim]

Alrighty. A bit more detail as well after I mess with it some more.

If I search for Police, it will just show the first hit and will put in the rest of the first hits label. If I hit Delete and get rid of the rest of the name so only Police is left, it'll show the rest of the findings. However this is counter-intuitive and I need to leave this as easy as possible for the person it's going to.

I got this part figured out. I changed the MatchEntry for the ComboBox to 2 instead of 0.

Thank you again for your help.

[mhkeim]

How would I get this to be applied to each cell in the Location Tab as well. So that when the user clicks or enters onto the next cell and starts typing out the location it will continue to use this tool?

I was working on a ComboBox that was going off of a Data Validation Table before if I remember right, but it was just a drop down you had to scroll through. Would it be possible to combine the two?