- #Drop down menu in excel 2016 how to#
- #Drop down menu in excel 2016 full#
- #Drop down menu in excel 2016 series#
Assign a sequential number to each relevant item found in the original list This formula is then used in our next step. You can see above that any item in column A that contains and E has a 1 beside it in column B, it doesn’t matter if the E is the 1st, 2nd, 3rd or 10th character, it will return a 1.
Copy this formula down column B, and let’s change our search field (H2) to search for the letter E. What is this doing? It is telling Excel, that if our SEARCH formula returns an error, then we want our formula result to be 0, or else we want our formula result to be 1. 
Our full formula is =IF(ISERROR(SEARCH($H$2,A2)),0,1) AFTER THE = SIGN, ADD “IF(ISERROR(” and then at the end of our formula add”,0,1)”.
We are going to add to the formula now in cell B2. In B3, you get the #VALUE! error, this is because the L can’t be found in APRICOT which is in cell A3. In B2 you can see the result of the formula is 4, the L is the 4th character in APPLE which is in cell A2. When we copy down the formula, we want all rows to be referencing cell H2, so make sure H2 is an absolute reference, with the $ sign before both the H and 2. In cell B2 we will enter a formula that will search for the value in H2 within the text in cell A2 =SEARCH($H$2,A2). To get us started, let’s enter an L in our search field (cell H2). Check each item in the original list against the search field Some of the formula we are about to use can look confusing if you’re not used to it, so I will start each one with a base formula and then add to it so we end up with the formula we need. Initially this will be a blank field, at this point we are not going to apply Data Validation to it. Column-D will be the shortened list, based on our search field, and our Data Validation will point to this list when completed. 
Column-C will assign a sequential number to each item in column A that meets our search field. Column-B will be used to check each item in column A against the search field in cell H2. While building my file, I will start with something like this They will be listed down in Column A of my file. Create and edit the Data Validation options to finalise the file. Create a Named Range for my drop-down list. Create a smaller list of items based on the search field. Assign a sequential number to each relevant item found in the original list. Check each item in the original list against the search field. I will then use some “helper” columns to create a smaller list of fruits and vegetables based on my search. In my example I start with a list of 88 fruits and vegetables. How To Create A Searchable Drop-Down List In Excel
I would advise you to read the earlier posts on Data Validation and drop-down lists, it is not necessary to do this, but they can give you an introduction to this topic. However, in reality I would have the list and helper columns on one sheet, usually hidden from the end user and they would be using the searchable drop-down list on another sheet. The list would contain all items (see the next image) and if you tried to search PP you would get an error message (see the second image below).įor this post, I have my original list, my helper columns and my search field all on the same sheet. The letters PP are entered in the cell and the drop-down list shows only the items containing PP.Ī normal drop-down list would not allow you to do this. The image above shows a searchable drop-down list.
This post will tell you how to create a searchable drop-down list in Excel.Īfter my earlier series of posts on Data Validation and drop-down lists, I received requests for further help and how to create a searchable drop-down list in Excel.
0 kommentar(er)
