Making Data Validation List Easier

[songgirl101]

Hi! I need some help making a data validation list easier to search/use. I have about 600 supplier names in my data validation list and it makes it really tedious to have to scroll down to find the right one in alphabetical order. Is there any way to make the list searchable? Also, if possible to make searchable it needs to be searchable in each individual cell as a new record is added almost every day with the supplier name (selected from the drop down list) and some other information. I have tried a combo box but it doesn't seem to work or I'm not doing it right. Any suggestions would be much appreciated. Thank you!

 

[Birdy01962]

Looking for the same function. I'd be interested in finding a solution.

At moment looking at Combo box but after your experience perhaps that might not be the wright way.

Thanks

 

[manu197a]

Well, without programming in Visual Basic it could be hard to make this easier. Are you willing to include macros in your file?

 

[songgirl101]

Well, without programming in Visual Basic it could be hard to make this easier. Are you willing to include macros in your file?

Yes! Macros are absolutely fine. I'm willing to use visual basic no problem just haven't figured out how it works with this type of question.

 

[Birdy01962]

I expect MVB is the only way. I'm a beginner at this though and spent a very unproductive weekend producing a Bill of Materials spreadsheet.
"Sheet1 - is list of material", "Sheet2 - Bill of materials. "

Column 1 enter materials via drop down fields - list of materials very large and looking for way to enter few initial letters to reduce the drop down options. e.g building materials: bricks, ballast, blockwork etc, from a list of 100's

Another thought based on this Bill of Materials form. If item not found in drop down list I would like the/another macro to add it to the inventory list. I have already created a macro that searches for next available cell in "sheet1 material list" for manual entry but not very user friendly.

Any help would prevent further hair pulling.

Many thanks

 

[Birdy01962]

Sorry to gatecrash your thread songgirl101 but between us we may get a solution.

 

[songgirl101]

Sorry to gatecrash your thread songgirl101 but between us we may get a solution.

No worries! Like you said I'm totally on board for finding a solution.

 

[manu197a]

Yes! Macros are absolutely fine. I'm willing to use visual basic no problem just haven't figured out how it works with this type of question.

I found these links with info that might help both of you:
In video: https://www.youtube.com/watch?v=Gm5m-y49rI0
In text: Create an Excel Drop Down List with Search Suggestions

Another one: https://www.youtube.com/watch?v=Jr34riKGveg

 

[dmt32]

Hi,
Have a look here:http://www.contextures.com/excelfiles.html#DataVal

and look for file: DV0023 - Limit Selection List

You can download a sample file for free which may be what you are looking for.

Hope helpful

Dave

 

[Birdy01962]

Thanks Manu197a and dmt32 I've looked at some of the youtube video and seems to do the job.

However the Drop Down menu field needs to be copied to extend a list. When I do this the Data Validation does not "appear" to be copied to the new location. It is not practical to edit every time I copy the drop down field.

Do you guys have a solution to this. Seem like a compromise either have a shorted limited selection on the drop down or wade through a long list of items.

Suggestions welcome. Colin