- #EXCEL FOR MAC DROP DOWN LIST HOW TO#
- #EXCEL FOR MAC DROP DOWN LIST CODE#
#EXCEL FOR MAC DROP DOWN LIST CODE#
Paste the code in the code window on the right. Double-click on the one that has the drop down list. In the VB Editor Window, on the left in the project explorer, you would see all the worksheet names. In the Excel workbook where you have the dependent drop down list, go to Developer tab, and within the ‘Code’ group, click on Visual Basic (you can also use the keyboard shortcut – ALT + F11). The credit for this code goes to this tutorial by Debra on clearing dependent drop down lists in Excel when the selection is changed. Here is the VBA code to clear the contents of a dependent drop down list: Private Sub Worksheet_Change(ByVal Target As Range) You can use VBA to make sure the contents of the dependent drop down list resets whenever the main drop down list is changed. When you have made the selection and then you change the parent drop down, the dependent drop down list would not change and would, therefore, be a wrong entry.įor example, if you select the ‘Fruits’ as the category and then select Apple as the item, and then go back and change the category to ‘Vegetables’, the dependent drop down would continue to show Apple as the item. Reset/Clear Contents of Dependent Drop Down List Automatically Using the SUBSTITUTE function within the INDIRECT function makes sure that spaces are converted into underscores. For example, when you create a named range with ‘Seasonal Fruits’, it will be named Season_Fruits in the backend. So when you create a named range using more than one word, Excel automatically inserts an underscore in between words. The reason for this is that Excel does not allow spaces in named ranges. Important Note: If the main category is more than one word (for example, ‘Seasonal Fruits’ instead of ‘Fruits’), then you need to use the formula =INDIRECT(SUBSTITUTE(D3,” “,”_”)), instead of the simple INDIRECT function shown above. This means that when you select ‘Fruits’ in cell D3, the drop down list in E3 refers to the named range ‘Fruits’ (through the INDIRECT function) and hence lists all the items in that category. How does this work? – The conditional drop down list (in cell E3) refers to =INDIRECT(D3). Now, when you make the selection in Drop Down 1, the options listed in Drop Down List 2 would automatically update. Here, D3 is the cell that contains the main drop down. In the Source field, enter the formula =INDIRECT(D3). In the Data Validation dialog box, within the setting tab, make sure List in selected. 
Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example). Fruits named range refers to all the fruits in the list and Vegetables named range refers to all the vegetables in the list. Doing this creates 2 names ranges (‘Fruits’ and ‘Vegetables’). In the ‘Create Named from Selection’ dialog box, check the Top row option and uncheck all the others. Go to Formulas –> Defined Names –> Create from Selection (or you can use the keyboard shortcut Control + Shift + F3). Select the entire data set (A1:B6 in this example). 
In Source field, specify the range that contains the items that are to be shown in the first drop down list.In the data validation dialog box, within the settings tab, select List.This will open the data validation dialog box.
Select the cell where you want the first (main) drop down list. Here are the steps to create a dependent drop down list in Excel: Creating a Dependent Drop Down List in Excel This is called a conditional or dependent drop down list in Excel. If I select ‘Fruits’ in Drop Down 1, I am shown the fruit names, but if I select Vegetables in Drop Down 1, then I am shown the vegetable names in Drop Down 2. You can see that the options in Drop Down 2 depend on the selection made in Drop Down 1. These are called dependent drop-down lists in Excel.īelow is an example of what I mean by a dependent drop-down list in Excel: 
Sometimes, however, you may want to use more than one drop-down list in Excel such that the items available in a second drop-down list are dependent on the selection made in the first drop-down list.
#EXCEL FOR MAC DROP DOWN LIST HOW TO#
Read: Here is a detailed guide on how to create an Excel Drop Down List. In the above example, I have used the items in A2:A6 to create a drop-down in C3. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.īelow is an example of an Excel drop down list: It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. Watch Video – Creating a Dependent Drop Down List in ExcelĪn Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards.
0 kommentar(er)
