How to - dependant dropdown lists from a dynamic set of data

Hi,

There are a few ways of doing this dotted about, have a look at the attached and see if it does what you want

Let me know if you have any questions

Hi kyle.. thanks for that quick response...

The file that you've sent me is really good. Looks like i have to just add extra products and the list updates by itself. I also believe, the scenario where i require consecutive columns to be filled up automatically - product description and relevant price - can be done by using 'VLOOKUP' function.

is there a way, to have the table ( from the file that you have sent me ) generated automatically from the 'original list' that i have ( screen grab in the first post ) . i.e. gathering of unique product manufacturers into one column and filling in the relevant products in another. and perhaps having the products sorted alphabetically.?

many thanks
Peter

Hi Peter,

Sorry missed that bit. Could you upload a sample of your workbook?

How often is the list updated and how large is it? If it is large it might be better to generate the grid when the workbook is opened/button pressed to stop the spreadsheet from getting a bit slow, this would need a macro though. Happy to do this, but can you have macros in your workbook?

Thanks

Azam,

Firstly I created a dynamic range for the Manufacturers, this allows more to be added and will automatically be added to the range. I use the following formula in the named range criteria:

Please Login or Register  to view this content.

Have a look here for more info on dynamic named ranges

I then created a secondary range, but horizontal rather than vertical, this contains the business names from the column headings:

Please Login or Register  to view this content.

The following dynamic range then looks at the value in the first box, then finds it in the horizontal range. The height of the range is then set by the count of products above the listed products. Manhori in the range below is the horizontal range, TRANSPOSE() is needed as match only works on a vertical list rather than horizontal, as this is an array formula it should be entered with ctrl+shift+Enter

Please Login or Register  to view this content.

Dear Kyle
Thanks a lot
i have little knowledge of using offset function particularly using height and width. could u guide me how to learn more about it.

Thanks & Regards

Have a look at the link I posted and here. Pretty sure that googling it will give you loads of tutorials

Thanks again