Use a selection from a list box to display a range.

I have a list box with 15 names of manufacturers. When the user selects a name from the manufacturers list, I would like to display a list of products made by that manufacturer. Further, after selecting a product, I would like to display a list of options for that product.

So, select a Manufacturer then display products. Select a product then display options.

I'm a rookie, please be kind.

Barry

I have a list box with 15 names of manufacturers. When the user selects a name from the manufacturers list, I would like to display a list of products made by that manufacturer. Further, after selecting a product, I would like to display a list of options for that product.

So, select a Manufacturer then display products. Select a product then display options.

I'm a rookie, please be kind.

Barry

Hi Barry,

I've attached a spreadsheet with a working solution. You need to have a list with all your manufacturers and then separate lists with manufacturer's product and another half a gazillion lists with each products's options.

Then you need to define range names for all these.

So far so good.

Next you set up data validation for the cell in which the manufacturer is selected, in my example Alpha, Bravo, Charlie, or Delta.

The field where the product is selected has data validation from the named range called secondList. Now here comes the trick:

The named range "secondList" gets dynamically defined by the name in the Manufacturer cell. This is also the name for another range, listing the manufacturer's products. So, if you select Alpha as manufacturer, the named range "secondList" will be equal to the named range called "Alpha". This is done by defining the range name "secondList" with the formula

=INDIRECT(Sheet1!$C$3)


The items in the named range "Alpha" get presented as a pick list for product.
In the example it is Alphaone, Alphatwo, Alphathree etc.

Now the same thing happens for the options (sorry, I called it features in my example). The drop down list for the options is with data validation of a list that is fed by the named range thirdList. Setup the same as above. If product selected is "Alphatwo", the range thirdList will be equal to the range Alphatwo.

The options list then displays the values listed in the range Alphatwo.

It probably won't make sense if you just read through this post, but if you open the spreadsheet, it should work.

Hope it all makes sense..
cheers

Teylyn

Thanks very much Teylyn. That appears to work well for me. One more question. Is it possible to call a picture for each of the featfures? A visual is always better for the viewer.

Barry

Barry,

the technique I explained works for text only, I'm afraid. I'm sure it is posible to stich something together with VBA to show pictures, but that's way beyond my depth.

cheers

Teylyn