Example:
Let say I have two sheets in a workbook
1) Lists
2) Answer
Sheet Lists : has two list as follows
Sheet "Answer" is as follows
1) Create Names as follows ( Insert > Names > Define)
| Name | Sheet Name | Formula |
| Type | List | =OFFSET(List!$A$2,0,0,COUNTA(List!$A:$A)-1,1) |
| List1 | List | =List!$B:$B |
| List2 | =OFFSET(List!$C$1,MATCH(Answer!$A33,List1,0)-1,0,COUNTIF(List1,Answer!$A33),1) |
2) Go to Sheet "Answer" select column A and select Data>Validation from Menu.
3) Select List from Allow drop down.
4) and write =type in Source textbox press OK.
5) Select the column B and Select Data>Validation from Menu.
6) Select List from Allow drop down.
7) and write =List2 in Source textbox press OK
