Pull-down "Data Validation List Dropdown" Formula where source lists are horizontal

[Dominik1455]

Hi,

I have this Excel Sheet from work, which has some tasks which we currently do manually. I hope there is a way to automate this with an excel formula.

There is a questionnaire in one tab, basically each row represents a question - there is another tab with standard answers for each question. For each question there are multiple(max5) standard answers. the "list" of answers are stored vertical, means the answers for question1 are "listed" in cells A1:A5, the answers for question2 in B1:B5, etc.

so far we used Data Validation to create a drop down list on each row of a question pointing to the cell range with the specific answers, so that users just can pick their answers from the drop-down. this has been made manually for each question.

Are there ways to make a “pull down”-formula within “Data Validation / List /Source” that does select a range and moves horizontally when pulled down?

I’ve managed to get a working formula together for a single cell:

=INDIRECT(ADDRESS(1;ROW(A1);;;"answers"))

Is the same possible for a range? Like:

=INDIRECT(ADDRESS(1:5;ROW(A1);;;"answers"))

Thanks for help and hints.

[#2]

@Dominik1455 

I'm quite sure the answer to your question is "Yes."

Although you've done a reasonably good job of describing your workbook's layout, I'm reluctant to try to create my own version of it....is it in any way possible for you to post either your actual workbook (so long as it contains no private or confidential information) OR a reasonable facsimile?

That would help me or others here return with a working example....I think you are already moving in the right direction, but it's just a lot easier to play around--if you'll excuse that phrase--with the real thing, making sure a recommendation actually works.

[#3]

@matheteshey thanks for your reply.

here is an simple example file with the current setup, where we manually change each range for the drop-down in sheet "questions" in column "C".

[#4 · Accepted solution]

@Dominik1455 

Here's one solution. I suspect there are others.

I've added a column (which should be hidden in actual use), Column C, on your tab of questions. It contains names that then are used in the data validation for Column D. That formula, in the Data Validation dialog box is =INDIRECT($C2) which can then be copied down to each question row. It thereby automatically is referring to a new "name" as the source for the specific list of answers pertaining to each question

On the answers sheet, those same names have been put in the top row, and then are used as assigned names to the ranges of answers.

[#5]

@mathetes

thanks, this is simple and works just fine for now!

btw: couldn't download your example, computer said it is "corrupted". However, it got the point using a reference to get my answer lists. perfect, thanks a lot!

[#6]

@Dominik1455 

Hmmm. Try this one. So far as I know, that "corrupted" has never happened before. [On the other hand, I am at a car dealership getting a car serviced, so not on my home network.]

But either way, glad it worked for you.