Conditional Data Validation

[swhitney]

Hello,

I am trying to set up some conditional data validation for a questionnaire type template. Each question will have an Index number and the various allowable answers will be on a separate tab.

Previously, I have set up a similar data validation with the following format and calculation:
=OFFSET(QuestionIDStart,MATCH(A1,QuestionIDColumn,0)-1,1,COUNTIF(QuestionIDColumn,A1),1)

[TABLE="width: 250"]
<tbody>[TR]
[TD]Question ID
[/TD]
[TD]Answer
[/TD]
[/TR]
[TR]
[TD]CO-01
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]CO-01
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]CO-02
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]CO-02
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]CO-02
[/TD]
[TD]15000
[/TD]
[/TR]
</tbody>[/TABLE]

While this method works, if the Question ID column is not properly sorted it can return unexpected results. I also just plain don't like the appearance for how I plan on using it.

I have been playing around with having the answers stored in the following format but I am struggling with getting data validation to find the right row and return only the non-blank values.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Question ID
[/TD]
[TD]Answer 1
[/TD]
[TD]Answer 2
[/TD]
[TD]Answer 3
[/TD]
[/TR]
[TR]
[TD]CO-01
[/TD]
[TD]Yes
[/TD]
[TD]No
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CO-02
[/TD]
[TD]500
[/TD]
[TD]1000
[/TD]
[TD]1500
[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions on how the data validation formula should be set up?

Thanks!!