Share this content
0
7
810

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.

Easy in Access

Really easy in Access using cascading combo boxes.

Thanks (0)

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)

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)

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)

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)