For a set of questions (of a questionnaire), I would like to fill in each answer (of a person) in an Excel sheet (see picture attached). I do it with a Drop Down menu and "data validation". Since I have a lot of questions, I don't want to manually chose the drop down range for each question. What I do instead, I just chose the range of the question with the most answers (in part 1) and apply the same formula for all other cells in part 2. But now I see always some empty values in the drop down choice (see picture). Any ideas on how to change this?

CodePudding user response：

Use this formula (properly edited) in your data validation:

=OFFSET(ADDRESS OF FIRST OPTION FOR THE QUESTION,0,0,COUNTA(OFFSET(ADDRESS OF FIRST OPTION FOR THE QUESTION,0,0,MAXIMUM POSSIBLE OPTIONS)))

For example, let's assume that:

1. the first option for the question 1 (that being 1.yes) is stored in cell B18;
2. the maximum possible options for any given question is 12;

then your formula should be:

=OFFSET(B$18,0,0,COUNTA(OFFSET(B$18,0,0,12)))

Note the partially absolute reference of the cell B18. It will allow you to apply the formula to each question (which are stored by the columns) and for multiple people (which are stored by the rows).