wetterman00
New Member
- Joined
- Jul 26, 2011
- Messages
- 1
Hi. Excel 2007 user on PC.
I am trying to prevent contradicting answers in a form that asks a series of survey questions. In each cell where the person being surveyed can submit an answer, I use a data validation list with the answer choices.
However, in some cases, depending on how an earlier question is answered, I would like the ability to block the users ability to answer a subsequent question.
I first tried having the data validation lists for the later questions tied to how the previous question was answered. For example, if prior question was "Yes" then the pop-down list is populated with something like "Already Answered", and if "No" then it would the pop-down list would have a series of other possible answers. The menu choices changed using an if statement based on the prior answer.
This is great except the cell still appears blank until the user selects the drop down list and sees "Already Answered" as the "option". My boss sees this as sub-optimal and would like to make it very obvious to the user that that question does not need to be answered.
Is the following scenario possible: the subsequent "answer" cell either pre-populates with "Already Answered" if the prior answer is "Yes" OR uses data validation to populate it with other answers?
Also tried conditional formatting but that can only change the color of the cell, not add text. Would like to avoid using VBA. Futzed around with the =INDIRECT() formula but not sure that's the solution either. Thank you.
I am trying to prevent contradicting answers in a form that asks a series of survey questions. In each cell where the person being surveyed can submit an answer, I use a data validation list with the answer choices.
However, in some cases, depending on how an earlier question is answered, I would like the ability to block the users ability to answer a subsequent question.
I first tried having the data validation lists for the later questions tied to how the previous question was answered. For example, if prior question was "Yes" then the pop-down list is populated with something like "Already Answered", and if "No" then it would the pop-down list would have a series of other possible answers. The menu choices changed using an if statement based on the prior answer.
This is great except the cell still appears blank until the user selects the drop down list and sees "Already Answered" as the "option". My boss sees this as sub-optimal and would like to make it very obvious to the user that that question does not need to be answered.
Is the following scenario possible: the subsequent "answer" cell either pre-populates with "Already Answered" if the prior answer is "Yes" OR uses data validation to populate it with other answers?
Also tried conditional formatting but that can only change the color of the cell, not add text. Would like to avoid using VBA. Futzed around with the =INDIRECT() formula but not sure that's the solution either. Thank you.
