Forum Discussion

Dominik1455's avatar
Dominik1455
Copper Contributor
Aug 12, 2020

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

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.

  • mathetes's avatar
    mathetes
    Aug 12, 2020

    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.

  • mathetes's avatar
    mathetes
    Silver Contributor

    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.

    • Dominik1455's avatar
      Dominik1455
      Copper Contributor

      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".

      • mathetes's avatar
        mathetes
        Silver Contributor

        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.

Resources