Progressive filtering of based on typed values in a drop down list in microsoft excel

R

rklugton

New Member
Joined
Apr 22, 2010
Messages
3
Hello all,

I have been racking my brain with this problem for the last few days and alas I have been left confounded with this problem and have no solution.

I want to create essentially a recipe spreadsheet by where users are limited in their choices that they put in the ingredients box for a predetermined list. The trouble is I have over 2000 different ingredients from which users can select.

I want to create a drop down list that is progressively filtered in the one column by the typed values inputed by the users. Ie when user enters s, all values beginning with s come up. Then the user types u so becomes "su" only showing values begging with "su" and so forth.

I am aware that the drop down list automatically jumps to the values on the list by the users inputting the first letter. But because of the large amount of values I have for example over 140 values for "s" this will be quite slow for users to scroll through and select. Also after reading on this site is that correct that the list function is only limited to 1000 values?

The second problem is that the values for the list will be located in a separate worksheet.

I am presuming that this function will require the programming of a macro as I don't think excel has this functionality built in any assistance with this would be greatly appreciated.

Thanks again,
Rob
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
If you use a from control list box and set the MatchEntry property to 1-fmMatchEntryComplete, then the user can type in and the selection will follow. So the user can type in 'sal' and the selection will show 'salsa, salt, salvia' etc.

You should be able to insert a form control in the spreadsheet. My preferred way is to always create a userform and have the controls there. It looks far more profesional and is easier to manage.
 
Upvote 0
Hi sijpie

Thank you for your help but where exactly do I put the match entry property in? In the list cell, in the data validation options window? Apologies as I am not that literate at excel.

Secondly because I will essentially have a separate excel database with the list with names and costs per kg I was thinking as the work around for this so as to not have to have this database open every time.

On a separate worksheet in the main user workbook which will be hidden have vlookup which is referring back to the main excel database. This list of values in the hidden worksheet will then form the drop down list in the main user worksheet. Because as far as I understand excel if I want to have a drop down list from an external workbook I have to have that external workbook open at the same time correct? This will prevent users having to open the two worksheets at the same time.

Thanks
Rob
 
Upvote 0
In Excel make sure that your drop-down list is a Form control or ActiveX control (depending on version of Excel)

Now if you open the control Properties, you will see this 'MatchEntry' option.
 
Upvote 0
Hi Sijpie,

I have found the controls which one do I want to use? I presume list box, the problem is when I use form controls "list box" i can enter the range name for the list to appear in the drop down list but I cannot get to the options screen to change the matchentry line.

I can although change the matchentry property when I use a active x control "list box" but I do not know where to put in the name of the range for the drop down list in this option.

I am using excel 2007. Thank you for your help.

Thanks
Rob
 
Upvote 0
Use the combo box or list box from the active-x controls depending on how you want to display it.

Click on the properties tab in the control box menu. This will bring up the properties where you have already found the MatchEntry property.
There is another property ListFillRange. Enter the range name of the list you want to display here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
237
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Data validation based on Sheet Name
Replies
5
Views
266
SuperX7
S
M
  • Solved
Drop down list? Or something else
Replies
1
Views
637
Herakles
H
S
  • Solved
Question about Drop down list
Replies
3
Views
230
RoryA
RoryA
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
495
MissDB101
M

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top