I have a data list of car makes and models in two columns. I want to create a dependent drop down list but it seems as though you need to store the data horizontally in excel rather than vertically according to my research. I have the make list as a validation list but only want to see the models for the particular make selected.
Does anyone have a workaround or an easier way of doing it? The data I have has some 55,000 records so I don't think any manual tinkering!
Thanks in advance
Replies (7)
Please login or register to join the discussion.
This may help
A blog article that may help is here ....
https://www.accountingweb.co.uk/blog-post/excel-tip-simple-dependent-drop-down-list
Good luck :)
As said above, it would be easier using Access!
It's not too difficult
as long as you sort your list by make.
For example:
Column A = Make (name range as "Make" ideally dynamically or by using a list) with the heading in A1
Column B = Model
Sort Columns A and B based on Column A alphabetically
Create dropdown list in say D1 downwards by choosing Data Validation, allow list.
In the formula box, type =OFFSET($A$1,MATCH(c1,Make,0)-1,1,COUNTIF(Make,c1),)
where C1 contains the model you are interested in.
So entering Ford in C1 will give a dropdown list of all Ford models in D1
You will need some error trapping but that should give you the gist of what you want.
Did you name
the Make range in Column A as "Make"?
See first line of my example. If you are unsure about the Dynamic range or using tables, just name the existing range in the normal fashion*. If that works for you, we can make the range name dynamic subsequently.
*i.e. Highlight all the entries in column A and then type "Make" followed by enter in the Name Box above Cell A1.
Access free runtime
Now I know that most accountants have Excel physically inserted at birth, but for handling data like this a database is much better. And since you can get a free runtime environment for Access, your client doesn't need to have their own program install.