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
There is no macro. I just loaded the same file I posted for you. the formula below is the only thing in cell I3.
=IFERROR(FILTER($G$3:$G$7,($G$3:$G$7<>$D$3)*($G$3:$G$7<>$D$4)*($G$3:$G$7<>$D$5)*($G$3:$G$7<>$D$6)*($G$3:$G$7<>$D$7)),"") Now, it may be that you don't have a new enough version of Excel installed. Or are you doing it on the web? The FILTER function is relatively new and may not be available everywhere.
But let's try it again, just to make sure that you didn't somehow get a corrupted version of the file.
mathetes- Thank you sir I will pass this on to the department. Have a great evening and be safe.