How to create dynamic list based on value of another list

[GuyGadois]

I have a simple spreadsheet that looks like the following...

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[TD]Product 4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]1.4%[/TD]
[TD]1.4%[/TD]
[TD]1.4%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD]5.6%[/TD]
[TD][/TD]
[TD]7.7%
[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]6.7%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1.2%[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD][/TD]
[TD]2.1%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD]5.5%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I wanted to have the ability to have a dynamic list generated where the user chooses a product (via a dropdown list called "Portfolio" and a list generates where it will give the list of the Item Number and number associated with it. Like...

Product 2
Item 1 1.4%
Item 2 5.6%
Item 5 2.1%

Product 4
Item 2 7.7%
Item 4 1.2%

Is this possible and how would I tackle this?

Cheers,

Guy

 

[GuyGadois]

Thank you! To greta solutions and I think the dynamic one fits best. Thank you!

GG

 

[Aladin Akyurek]

Thank you! To greta solutions and I think the dynamic one fits best. Thank you!

GG

If you mean to opt for VBA, it's ok. The formula set up can be made also totally dynamic by using dynamic named ranges.