Share this content
0
7
840

Excel dependent list

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

Please login or register to join the discussion.

avatar
By neileg
25th Feb 2013 12:39

Easy in Access

Really easy in Access using cascading combo boxes.

Thanks (0)
25th Feb 2013 12:44

This may help

A blog article that may help is here ....

http://www.accountingweb.co.uk/blog-post/excel-tip-simple-dependent-drop-down-list

Good luck :)

As said above, it would be easier using Access!

Thanks (0)

Thanks

I have already seen the article and really cannot warrant the time that would take to reorganise the data.

Might have to go with access but this is for a client(s) to use so wanted to use excel.

Thanks (0)
avatar
25th Feb 2013 14:33

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.

 

Thanks (0)

Thanks Paul

I tested your formula in the list box and received the below message

A named range you specified could not be found

Thanks (0)
avatar
25th Feb 2013 20:35

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.

Thanks (0)
avatar
By neileg
26th Feb 2013 13:04

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.

Thanks (0)