Excel drop-down list, aka drop down box or combo box, is used to enter data in a spreadsheet from a pre-defined items list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user.
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! 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.
You can import your photos, videos and docs to the drive from popular social media and cloud storage services like Facebook, Instagram, Dropbox and Google Drive with the included WD Discovery software. WD My Passport 4TB portable external hard drive for Mac is ready to use out of the box so you can start transferring files, saving your valuable memories and backing up right away. Perfectly paired with Apple’s Time Machine backup software and password protection to protect your photos, videos, music, and documents. My passport for mac installation.
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. Solitaire app for mac free. 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. • Jeff Weir Great post. Note that you can do away with MyItemList (and hence the the volatile OFFSET function it contains) altoghether by using the below formula in place of your MyItem formula: =INDEX(Table1,1,MATCH(Table2[@Category],Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Table2[@Category],Table1[#Headers],0))),MATCH(Table2[@Category],Table1[#Headers],0)) (and as an added bonus, there’s no need to select a particular cell when entering this formula) You can then delete the MyItemList nameit’s no longer needed.