Drop down lists are created in Excel by using data validation. Data validation lets you specify what information appears in your custom Excel drop down list. Using drop down list lets you pick what information is added to your spreadsheet. Add a drop down list in areas where you want to control the content. Nov 21, 2018 - Create drop-down lists, or menus, in Excel to limit the data that can be. Versions 2019, 2016, 2013, 2010, Excel Online, and Excel for Mac.

The other day I was reading a post over at the Contextures blog about and realized that using an Excel Table would provide an alternative method that is both simple and flexible. Tables are available in Excel versions 2007, 2010, and 2011. In this post I’ll create a Table to hold the Category’s and Items, create three defined names using dynamic, then use Data Validation to create two drop-down lists, the second being dependent upon the first.

Create at Reference Table Here’s a Table with Category names in the header row and Items in the columns. I just typed in the information then.

Create a Dynamic Defined Name for Category List Create a defined name for the Table1 Header row range by using the formula =Table1[#Headers]. Please note that Table1 is the name of the Table created in the step above. This defined name is dynamic, meaning it will expand when more columns are added and shrink if any columns are deleted. It will return the header row of the Table, which we’ll use in the next step. I used myCategory for this defined name. • Manu Hi Greg, Great post! It has helped me a lot.

I have used it to create a dynamic drop down on a column of a table that reads the drop down list values from that same column on that same table. It is a great feature since it allows either selecting existing values or entering new ones. However, when I enter one value on the column more than once it also appears more than once in the drop down list. I have not been able to figure out a way of eliminating duplicates in the drop-down. But I am not a excel semi-pro as you 😉 Do you have any hint to give me? Thanks a lot and keep the great work here! Hp envy 7645 printer driver for mac free. Cheers, Manu.

• Matan Hi Gregory. Thank you for the lovely post and answers.

I have a problem with the dependent lists, and I see it’s also in the sample excel file you’ve distributed, and would really appreciate your assistance on this matter. The thing is that the secondary list doesn’t update when I change items in the main list. For example, in your excel file, if I change A2 from fruits to vegetables, B2 still displays “Banannas”. Only when I actually click on the drop-down menu in B2 do I see the vegetables. Is there any way to update the displayed value in B2 once I switch items in A2? • Post author Assume for a moment that you have a Defined Name Formula that is used in Data Validation for cell B2, and the name is MyFormula and it equals SuperDuperFormula.

If you want MyFormula to recognize when there is a blank cell in A2, then you should modify the formula as follows. First select cell B2, then go to the defined name formula MyFormula and change it to: =IF(A2=””,””,SuperDuperFormula) This modification to the formula will look to the first cell that is to the left of the active one, and if there is a blank it will return a blank, otherwise it will execute the SuperDuperFormula.

For the most part, this approach works. However I believe that there is some event-driven behavior with the Data Validation that sometime interferes. Hope this helps. • Forgot to mention that you have to download the file by clicking File then Save As. I’m not clear how the IF statement helps once a selection has been made in B2 and then the selection is changed in A2.

I think Matan is talking about this scenario: 1. The user selects “Vegetables” in A2 2. The user then selects “Cabbage” in B2 3. The user then selects “Fruit” in A2. Granted, your IF statement is handly for making sure that you can’t pick a seleciton from B2 UNTIL you’ve made a seleciton from A2. But it doesn’t help with the above scenario, where you in fact DID make a selection from A2, then made a valid selection from B2, but then subseqently changed your selection in A2 making the data in B2 out of context.