Another option:
In Raw Data, go to Formulas|Define Name
create Name Suppliers and use formula to define it:
=OFFSET('Raw Data'!$B$2,0,0,1,COUNTA('Raw Data'!$2:$2))
then create a Name called All with formula:
=OFFSET('Raw Data'!$B$2,0,0,10000,COUNTA('Raw Data'!$2:$2))
(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:
in B2, go to data|Validation, choose List and enter formula:
=OFFSET(Suppliers,1,MATCH(A2,Suppliers,0)-1,COUNTA(INDEX(All,0,MATCH(A2,Suppliers,0)))-1,1)
Bookmarks