Forum Discussion
Survey Ranking Validation
The simple answer to your question is "Yes."
But I assume you didn't really want just that one word. So let me just say, you'll need to define it a bit further.
- First of all, is that really your question, or was that just a way to illustrate your actual situation?
- Second, if that IS your real situation, is it correct to assume that if in Question #2, the answer was "C" then for the remaining questions, both A and C are off limits? And so forth...so that by the fifth question there's only one valid answer?
- But is that, in fact, what you'd really want?
- Is the context here a test, in which you might in fact want to give them all five possibilities for each question, regardless of what they'd chosen before.
Etc.
It is possible to do what you asked, but it would involve a set of lists of all the possible combinations in sequence. Or dynamically creating new lists each time for the data validation. A lot depends on a far more complete description of the situation.
mathetesThank you for your reply. Yes, you are correct - The final question would have only 1 choice (really not a choice) but only remaining option.
This was fun. I added a brief explanation. The solution makes use of the FILTER function, one of a small set of new Dynamic Array functions.
mathetesReceived the #NAME reference error and the following formula is all that appears in I3
=_xlfn.FORMULATEXT(I3)
Once I click on the enable macro everything displayed in the =ifERROR formula disappears
