![]() For example, if he chooses employee ID, which is a unique value, other fields should automatically be filled. Sometimes, when a user selects a drop-down element, he doesn’t need to fill in other data. If you haven’t chosen an option from the “Food type” list, you are going to get this message:Ĭlick Yes, and the second drop-down will start working the moment you choose a value from the first one. Depending on the drop-down value we choose, we have either Fruit or Vegetable, and the function returns these named ranges created before. The INDIRECT function takes a text reference as an argument For example, =INDIRECT(“B3”) returns “Carrot”. Enter the formula with the INDIRECT function. Now, if you go to the Name Manager, you are going to find two additional named ranges: Fruit and Vegetable.Ĭlick cell E2, and open Data Validation. Go to Formulas > Defined Names > Create from Selection.įrom the new window, we want to uncheck the Left column because we want named ranges for fruit and vegetable types, and not for each fruit. This time select the entire table ( A1:B6). Next, depending on this choice, the other drop-down will display the relevant data.įirst, under the “Food type”, we are going to add a drop-down.Ĭlick cell D2 and add a new drop-down list. ![]() In the following example, we are going to select a food type: fruit or vegetable. Dependent Drop-down Listsĭependent drop-down lists are the lists where one drop-down is dependent on another. But if you use a different case, you will get an error. Now, if you type one of these values, Excel won’t complain. You have to enter the exact case in the drop-down. If you type a list of options directly inside the source textbox. You can use whatever case you want, even some weird ones, and Excel is not going to complain. But it doesn’t mean that you have to enter them using the same case. If you enter values that are not inside the drop-down list, you will get an error. Now, if you try to enter a value that is not on the list, you will get an error. In the Data Validation window, you have to uncheck the Ignore blank checkbox. There is an option you can check which will force Excel to return an error. It will return an error if you enter as a source the same range or you name a range without any empty cells inside. Now, if you try to enter any name it won’t return an error. Navigate to Formulas > Defined Names > Define Name and call it “withEmpt圜ells”. Now, if you add this selection as a source of a drop-down and try to type a value that is not present, you are going to get an error, but Excel behaves differently if you give this range a name and try to use it instead of a range. But look what’s going to happen if you define a name for a list. Even if you create a drop-down list with empty cells. Entering any valueīy default, you can’t enter values into a drop-down list that are not present inside the source. Now, when you add additional elements they will be visible inside the drop-down. This formula adds all cells from column A, but the first one (header). Instead, use the formula with the OFFSET function. The last option is to select the whole column.īut you can’t type just =A:A when you have a header because it will be added to a drop-down list. ![]() You can encounter them if you add an element to the normal range, and you forget to change source data.Īs you can see, the source had been selected before the last two options were added that’s why they are not present inside a drop-down list.Īpart from changing the source each time or using tables, there is a third option to deal with this problem. If you define a range for table elements as you did before, you shouldn’t encounter any problems. There are a few problems you can face when you work with drop-down lists. You can also right-click the worksheet tab and select Protect Sheet. You can add a password, otherwise, people can unprotect it. To do it, select a sheet you want to restrict, and navigate to Review > Protect > Protect Sheet. You may want to protect this worksheet, so nobody can make changes to it. We usually place data used in a drop-down list on the second sheet.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |