Conditional Data Validation - Index & List Functions

[ShawnExcel]

I have a table with 100 questions, and another table with 500 answers. Each question has an ID number, and each answer is assigned to one of those 100 ID numbers.

I want to insert a column next to the questions where you can select from certain answers. How can I write a formula in the data validation list box that ONLY shows potential answers that are assigned the ID number of that question?

Table 1:

• ID Number (Unique)
• Question
• Answer Column

Table 2:

• ID Number (Assigned to one of the ID numbers from Table 1)
• Answer
• Answer Description

My goal is that each cell in the "Answer Column" in Table 1 has a dropdown list with the Answers from Table 2. I would think I could do this using Index and Match, but I cannot get it to work. Any ideas?

Thank you!

 

[NARAYANK991]

Hi ,

Please upload a sample workbook with at least a few questions and a list of answers to select from.

Narayan

 

[Tiago MF]

Hi Shawn

Please see if the attached file helps. You don't really need to set ID numbers but I guess there are solutions for your problem that can use that.

Anyway, paste all your questions and answers on aux sheet and adapt the ranges on the data validations of sheet2 and it should be OK.

 

[ShawnExcel]

Hi ,

Please upload a sample workbook with at least a few questions and a list of answers to select from.

Narayan

Here is a sample with only a few things filled in. I will look at Tiago MF's spreadsheet next. Thank you both for the help

 

[ShawnExcel]

Could you help me understand the code within the formula for data validation?

I cannot apply it correctly to the Sample Excel sheet I sent to you as I cannot get it to correctly count the number of times the question ID appears in the Answers table. I also have the questions listed vertically instead of horizontally. Thank you for your help

 

[Tiago MF]

Hi Shawn

Please see the file attached with the data validation applied to your file.

The data validation is using the named range "Question_ID". You can define similar named ranges for other columns where you want to include data validation using the same logic.

Regards,

 

[ShawnExcel]

Thank you! Your updated one worked perfectly for me. I greatly appreciate it.