fertmonkeys.blogg.se - Create a selection list in excel

CREATE A SELECTION LIST IN EXCEL HOW TO

Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club. On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.

Here, we ignore the 1st row of ( $B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s. up to 9th row number of this ( $B$4:$D$12) array of INDEX function. Actually, it should be ROW(B2:D9)=> which denotes respectively the 2nd, 3rd, 4th…. Here, ROW function is working as the row index number of the INDEX function.

CREATE A SELECTION LIST IN EXCEL HOW TO

How to Turn Off AutoComplete in Excel (3 Easy Approaches).How to Edit AutoComplete in Excel (4 Easy Methods).Turn on Autocomplete in Excel (3 Easy Ways).How to Plot an Equation in Excel (6 Easy Ways).You can visit our site for more articles about using Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. If there is a space in a name, then we could use “ _”.Īt the end of this article, I like to add that I have tried to explain 6 smart ways to populate a list based on cell value in Excel. In terms of giving the name of the list, we cannot use any space.

We need to take the help of the IFERROR function to handle this error. If the formula does not find any data from the dataset, then it will return this error. The include argument must have dimensions compatible with the array argument, otherwise, FILTER will return #VALUE!

Input the following formula in Sorce and press OK.

Now, select all the cells under the Food Type column to create a drop-down list in each selected cell with the Food List column.

Similarly, I have named Dairy, Vegetables, and Fruits for the other three column members.

I have named the cells under Foods List as Foods. Just follow the following procedures to do so. In order to create a dependent drop-down based on a cell value, we can use the INDIRECT function. Use INDIRECT Function for Dependent Drop Down List Read More: AutoComplete Not Working in Excel (2 Possible Solutions)ģ.

Now, press ENTER to have the desired output.

C19 will give the input name and will compare with the name range C5:C17.

In the FILTER function, B5:B17 is the range from where we will extract the data.

Now, insert the following formula in C20 to find the projects related with that person’s name.

I want to find the projects based on person’s name.

Here, I have project name along with the person name on that project. We can also use the FILTER Function to populate a list based on a cell value. Apply FILTER Function to Populate a List Based on Cell Value Read More: How to Use AutoComplete in Excel (4 Easy Ways)Ģ.

You can change the club’s name from the list in cell C14 and the players’ names will auto-update.

Finally, press ENTER to have the players’ names from the defined club.

Lastly, IFERROR is to handle any kind of error.

If the data is not found in the given dataset then it will be through #NA error.

INDEX($B$4:$D$12, ROW(B3:D12), MATCH($C$14,$B$4:$D$4,0))is the portion of the formula which is used to find the matched player’s names.

ROW(B3:D12)is counting the rows number of the dataset.

Here MATCH($C$14,$B$4:$D$4,0)this portion is matching the entered Club Name with the Dataset and it is considered only exact matching.

Now, select a cell where you want to have the players’ names and input the following formula in that cell.

Now, you can choose any club name from the list.

Now, pick List from Allow under the Settings tab.

Afterward, click on Data Validation from the ribbon.

In order to create the club names list, select cell C14 and go to the Data tab.

My purpose is to autofill the player’s name just from the club’s name. Here, I have created a dataset with players from different clubs.

First of all, create an organized dataset.

With the help of a formula combined with the IFERROR, INDEX, and MATCH functions, we can autofill a list based on cell value. The methods are described in the following section. 6 Smart Ways to Populate a List Based on Cell Value in ExcelĪmong many other methods, I am going to explain 6 smart ways here to populate a list based on cell value in Excel with the help of the INDIRECT, IFERROR, INDEX, MATCH, IF, FILTER, UNIQUE, and some other functions.