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)

Question ID	Answer
CO-01	Yes
CO-01	No
CO-02	500
CO-02	1000
CO-02	15000

<tbody>
</tbody>

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.

Question ID	Answer 1	Answer 2	Answer 3
CO-01	Yes	No
CO-02	500	1000	1500

<tbody>
</tbody>

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

Thanks!!