Help with Dynamic Dependent Validation lists

This one has been driving me mad!

I am trying to create a multiple data validation list. The 1st list is Suppliers, and the 2nd dependent list is the products of these suppliers. The hard part is i need both these lists to be dynamic, so products and suppliers can be added easily from a seperate sheet. Also some of the manufactures and products have two part names.

Thank you for any help

Hi and welcome to the board. Below are list of the websites where you can find useful information regarding your issue. Hope it helps)))

http://www.contextures.com/xldataval02.html
http://www.contextures.com/xldataval13.html
http://blog.contextures.com/archives...idation-lists/
http://www.contextures.com/xldataval15.html
http://www.ozgrid.com/Excel/dependent-lists.htm
http://www.ozgrid.com/News/excel-dependent-lists.htm

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

First type Subtrader labels anywhere in one column. Say this will be G2:G4, then name this area as "Subtrades" using
=OFFSET('Raw Data'!$G$2,,,MATCH("*",'Raw Data'!$G$2:$G$50,-1))

Go to Templates sheet, highlight A2:A30 and go to Data > Validation. In dialog box From "Allow" dropdown choose "List", then in "Source" field just type "Subtrades" (w/o quotes)

Then highlight B2:B30 of Template sheet, o to Data > Validation. In dialog box From "Allow" dropdown choose "List", then in "Source" field just paste below formula
=OFFSET('Raw Data'!$B$2,,MATCH(Template!$A2,Subtrades,0)-1,COUNTA('Raw Data'!$B$3:$B$50),)

Sample is attached)))

Another option:

In Raw Data, go to Formulas|Define Name

create Name Suppliers and use formula to define it:

Please Login or Register  to view this content.

then create a Name called All with formula:

Please Login or Register  to view this content.

(where 10000 represent the max number of rows you will ever need for this tab)

then in Template sheet A2, go to Data|Validation, choose List and enter formula:

Please Login or Register  to view this content.

in B2, go to data|Validation, choose List and enter formula:

Please Login or Register  to view this content.

Great

Please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save