papalazarou
Blogger
Share this content
0
7
877

Excel dependent list

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)
avatar
25th Feb 2013 13:22

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)
avatar
25th Feb 2013 19:31

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)