Is there and easy way to search a long drop down list? Predictive text? Or something?

[HUNTXTRM]

Is there and easy way to scroll through a long drop down list? Can you set up a drop down box to predict what you want, compared to what is in the drop down list? Thanks

[protonLeah]

If you are using Data Validation, you can switch to an ActiveX combobox.

[HUNTXTRM]

I see yours working, but I guess I don't understand where to go on the ribbon to find combo box?

[HUNTXTRM]

Ok, figured out how to add in combo box, now I cant figure out how to work it? LOL

[protonLeah]

Suppose you have a data val list in B1 with with you want to select the month from a list. Then to switch from the in cell data validation to the combobox,
1. cover the cell B1 with the combobox .
2. on the ribbon, developer tab, click DESIGN MODE
3. Right-Click the Combobox, choose Properties
4. Enter the address of the cell range to polulate the list with in the ListFillRange box
5. Enter the linked cell, in this case B1 since that's where the data val dropdown was.
* Make sure it's an ActiveX combobox not a Forms combobox.

[HUNTXTRM]

Suppose you have a data val list in B1 with with you want to select the month from a list. Then to switch from the in cell data validation to the combobox,
1. cover the cell B1 with the combobox .
2. on the ribbon, developer tab, click DESIGN MODE
3. Right-Click the Combobox, choose Properties
4. Enter the address of the cell range to polulate the list with in the ListFillRange box
5. Enter the linked cell, in this case B1 since that's where the data val dropdown was.
* Make sure it's an ActiveX combobox not a Forms combobox.

Refering to your number 4. My list is on another page, called 'Material Costs". I entered 'Material Costs'!B4:B258 But, it didn't work. will it refer to a list on another page?

Thanks

[HUNTXTRM]

Play thing TEMP.xlsxWell now I have it really jacked up. Says "Reference not Valid" and wont let me click on anything, or delete, or go back or go forward. What have I done. LOL Im going to put my sheet on here, if you could help me through this, it would be greatly appreciated. I am a noobie of sorts to excel. been tinkering with it for a while, no instruction. I am learning, if that's any consolation. LOL

[HUNTXTRM]

ScreenSheet.jpgHere is an image of the locked screen.

[protonLeah]

I assume that you want the dropdown list for the "components" column on the MATERIAL COSTS worksheet, but that column has more that 400 rows, so you can't use an activeX box in each cell.

One alternative is to break down the parts description list into several NAMED RANGES and use data validation in those cells.

In the attached workbook, I created seven named ranges based on the the section headers on the Material Costs sheet. Then I inserted a new column C and used the section titles from the MATERIAL COSTS sheet to create a validation list. Then use those named ranges for smaller validation lists for the Component column.

An another way would use a pop-up user form with the list

[HUNTXTRM]

Thank you for the examples, I will look at them. Sounds like that might be a fit. I really appreciate the help!

[HUNTXTRM]

That will work great! How did you create the data validation? You have solved my problem. Thanks!

[HUNTXTRM]

I can't figure out how you made the "Component" drop down, refer back to the "Material Group" drop down? I tried entering the data validation, just like in your pic, but my list is blank, in the "Component" Drop down? When I select something in the "Material Group" Drop down?

[protonLeah]

For the material list.xlsx workbook,

The group titles in column B of the "material costs" sheet are relisted in column J of that sheet. That group of cells is then named "MaterialGroups" so the validation formula used in column C of the "Materials" tabe is just "=MaterialGroups". Then, in column B, I created seven named ranges, for example GRC AND ACC spans B5 to B86. Since the list items used in the validation dropdown have spaces and named ranges can't have spaces in the names, "GRC AND ACC" from the list becomes "GRCANDACC" for the named range. The named parts list ranges are:

Please Login or Register  to view this content.

The data validation in column C uses the Group name. The data validation in column D uses the formula "=INDIRECT(SUBSTITUTE($C6," ",""))" to remove the spaces from the name and fill the list with the named range.

For "material listpop-up(bvj).xlsm" no data validation is used.
I created a small form with the combobox that uses the entire parts list and a couple of buttons. Then I wrote a worksheet selection change macro. It tests to see if the cursor in in column C between rows 6 and 424. If so, it shows the form.

Please Login or Register  to view this content.

The form just puts the selected list item in the active cell.

[HUNTXTRM]

Appreciate all the help, learned something.