Creating a dynamic range drop down list from awkward table.

[ct2911]

Hello everyone

I'm trying to think of a way to create a conditional drop list from a table which is updated daily meaning the ranges of each list and sub list will change.

I am using excel 2007.

I know how to create a conditional drop down list however the table format I have to use makes it a little more difficult.

Below is just a sample of the worksheet format I have to use. The real thing will have roughly 300 companies.


I Book drop down list will be conditional the company selected

E.G. If I select "Tesco" from the company drop down list, I will see "TTTG","BGTY" and "PLMN" in the book drop down list.

I was thinking some sort pivot table style maybe ?

Company	Book
Argos	TTLF
Argos	POIU
Argos	TTTG
Argos	DFOP
Argos	QWER
Argos	PPPP
Tesco	TTTG
Tesco	BGTY
Tesco	PLMN
Nike	TTLF
Nike	TTTG
Nike	FLJH
Nike	ASDF
Nike	PMGW

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Many Thanks to you all in advance and thank you for all the prior threads, I have learnt a lot from the Mr Excel forums.

CT2911

 

[NeonRedSharpie]

Add a helper column next to your list. The helper column will be an if statement. Let's say your drop down in is A1, your company column is in D1, and your Book column is in E1. Your list for Books will be in E2.

In G1:

=IF($D1 = $A$1, $E1, "")

Then, your data validation list should be pointing to $G:$G. Ignoring blanks should (might) remove (might not) all the blanks.