![use in formula drop down box excel 2016 use in formula drop down box excel 2016](https://www.contextures.com/images/datavalidation/createdropdown15.png)
Choose List and enter the following formula =myItem, then click OK. Select cell B2 and open the Data Validation dialog box. I named this defined name myItem, as you can see below in the Name Manager screen shot.Ĭreate an Item Drop Down List with Data Validation
![use in formula drop down box excel 2016 use in formula drop down box excel 2016](https://www.wikihow.com/images/9/94/Add-a-Drop-Down-Box-in-Excel-2007-Step-11-Version-3.jpg)
This formula uses the first defined name ( myItemList) but alters the height by counting the items. =OFFSET(myItemList,0,0,COUNTA(myItemList),1) The following OFFSET formula will do the trick. We can create a second defined name that will give us a dynamic list with the exact count. However, if you have a table that always has equal items (rows) in each column then this defined name formula will will work well for a Data Validation list. As you can see in the picture below, the Vegetables item list has two blank lines, and the Other Stuff item list has one blank line. If I use Data Validation to create a drop-down list with the myItemList defined name I’ll get eight items returned because Table1 has eight data rows. This formula will return a reference to the Table column that matches the Category selection in cell A2 on Sheet1. Create the First Defined Name for ItemsĬreate a defined name with the following formula: This takes two defined names to work properly. (Don’t worry about extending this Data Validation down to more rows, we’ll take care of that later.)Ĭolumn B will hold a drop-down list for the Item, which is dependent upon the Category. Now cell A2 has a drop down button that shows the Header row of the Table. Next select cell A2, then choose Data > Data Validation > Data Validation… and in the Data Validation dialog box (shown below) select List from the Allow box, then type in =M圜ategory in the Source box, and click OK. Type Category in cell A1 for the column heading. Create a Category Drop Down List with Data Validation
![use in formula drop down box excel 2016 use in formula drop down box excel 2016](https://images.saymedia-content.com/.image/t_share/MTc1MDE0MzU5NjI1MTE1NDM1/how-to-create-a-list-box-in-ms-excel-2016.png)
This will bring up the New Name dialog box that looks like the Edit Name screen-shot shown above.
#Use in formula drop down box excel 2016 windows#
To create a defined name in Windows choose Formulas > Name Manager then click New…. It will return the header row of the Table, which we’ll use in the next step. This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. Please note that Table1 is the name of the Table created in the step above. I just typed in the information then converted to a Table.Ĭreate a Dynamic Defined Name for Category ListĬreate a defined name for the Table1 Header row range by using the formula =Table1. Here’s a Table with Category names in the header row and Items in the columns. In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic formulas, then use Data Validation to create two drop-down lists, the second being dependent upon the first. Tables are available in Excel versions 2007, 2010, and 2011. The other day I was reading a post over at the Contextures blog about Dynamic Dependent Excel Drop Downs and realized that using an Excel Table would provide an alternative method that is both simple and flexible.