VLookup / Valdation Help

[stupointzero]

Hi Guys,

I'm trying to figure out how to acheive something quite simple, but have become a little stuck...

I'd like a dropdown box in A2 to select from a list of 2 products, then in B2 i'd like to be able to have a dropdown box to select the product variation. The products and variations will be located from D1 to E4 and I've tried to demostrate this in the table below. I think perhaps i need some kind of VLookup but am not sure, are you able to advise?

Many thanks in advance.

Stuart.

[TABLE="width: 400"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Variation[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Variation 1[/TD]
[TD]Variation 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Variation 2[/TD]
[TD]Variation 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Variation 3[/TD]
[TD]Variation 3[/TD]
[/TR]
</TBODY>[/TABLE]

 

[jamtay317]

ok waht you need to do
1.select the range that you want to be in b2 in this case it would be D1:E4 push Ctrl+T
2. then select d1:D4 and push Ctrl+shift+f3 ( Keep top row checked) push enter
3. select e1:e4 push Ctrl+shift+f3 ( Keep top row checked) push enter
4. select A2 push Alt+D+L
5. Choose list
6.Under Source copy paste

=$D$1:$E$1

or (choose your first Drop down range)
7.select b2 push Alt+D+L
8. Choose list
9.Under Source copy paste

=CHOOSE(MATCH($A$1,$D$1:$E$1,0),Product_1,Product_2)

push enter

 

[stupointzero]

thanks for the walkthrough, when i've clicked OK in the data validation box after step 9 i get the message "The Source currently evaluates to an error. Do you wish to continue?"

I've clicked Ok but then the drop down box in B2 doesnt work?

 

[Peter_SSs]

I think that formula should be

=CHOOSE(MATCH($A$2,$D$1:$E$1,0),Product_1,Product_2)

 

[stupointzero]

perfect, thanks guys! :D