Combining Lookups and Dependent Drop-down lists

[weeksy]

Hi all

Everyone was helpful with my last query so I hope someone can help with this one. I'm on windows 7, excel 2013.

I have a workbook whereby a user has to select a territory in sheet 1. Once they select the territory they need to be able to select a film rating for that territory. I want to restrict the list of the available ratings to that territory so they cannot accidentally select one for a different country. I want the end result to look like this.


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Territory[/TD]
[TD]DE[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Ratings System[/TD]
[TD]FSK[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Rating[/TD]
[TD]12+[/TD]
[/TR]
</tbody>[/TABLE]







This is how I think I need to get to this but I'm uncertain how to construct the formula as this is too advanced for me.

1. User selects territory from drop down list in B1. (there are 124 territories to choose from and the drop down list is constructed from column A on Sheet 2)
2. Match/Lookup the value in B1 in the table in Sheet 2 in cells A2:A125 and return value in Sheet 2 column C
3. Based on the value in B2 there is a dependent drop down list in B3. The list comprises the values in the relevant range.

I have the ratings on sheet 2 in the below format.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DE[/TD]
[TD]Germany[/TD]
[TD]FSK[/TD]
[TD]ALL[/TD]
[TD]6+[/TD]
[TD]12+[/TD]
[TD]16+[/TD]
[TD]18+[/TD]
[TD]UR[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GH[/TD]
[TD]Ghana[/TD]
[TD]GH[/TD]
[TD]U[/TD]
[TD]A[/TD]
[TD]X[/TD]
[TD]UR[/TD]
[TD]XX[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to find the value in Sheet 1 B1 in sheet 2 column A. In the example given it is "DE" for Germany.
Having found DE in Sheet 2 column A I want Sheet 1 B2 to populate with the value in Sheet 2 C1 - "FSK".
The drop down list in Sheet 1 B3 should then be limited to values in Sheet 2 D1:I1. I have made D1:I1 a range called Germany. Each country has its own range (you can see a Ghana example underneath Germany), so 124 in total.

I've found examples on the web and on this site in particular but nothing combining matching or lookups AND dependent lists. Any help or directions to previous queries which do match this criteria would be much appreciated.

Thanks!

 

[Fowmy]

Hi,

Enter below formula in sheet1 B2

=VLOOKUP(B1,Sheet2!$A$1:$I$125,3,0)

Set up Data Validation rule and for the formula of LIST type enter below formula to show up in sheet1 B3

=INDEX(Sheet2!$D$1:$I$125,MATCH(B1,Sheet2!$A$1:$A$125,0),)

 

[weeksy]

Thanks Fowmy.

The second part works beautifully. The first part is giving me a #REF! though. Could that be because B1 contains a value from a drop down list rather than typed text?

 

[weeksy]

I fixed it - Thanks again for your help. Much appreciated.

Thanks Fowmy.

The second part works beautifully. The first part is giving me a #REF! though. Could that be because B1 contains a value from a drop down list rather than typed text?

 

[Fowmy]

You are welcome !