Dynamically changing dropdown in excel.

Question

The scenario is as follows,

I have a list of options to be filled in one cell, say A1. The options can be filled using Data Validation-List which causes a dropdown to appear in A1. Now I want B1 to contain a dropdown, the entries in which change on the basis of the data in A1.

As a contrieved example, suppose A1 offers the choices Mammals, reptiles and amphibians. If I select mammal, I should get man, cat, dog as an option in B1. If I select Reptiles, snake and lizard appear as an option in B1. If I select amphibians, I should get Frogs and toads.

I would like to avoid using VBA for this.

Answer 1

Use the INDIRECT formula in the validation list as is clearly explained here:

www.contextures.com/xlDataval02.html

Answer 2

Here you go, a solution completely without VBA. It's using actual combo box controls from the "Forms" toolbar:

• Add three extra worksheets to your workbook. I called them "domain", "data" and "animal"
  • on sheet "domain", I did:
    • enter (from cell A1 downwards) "mammals", "reptiles", "amphibians"
    • defined a name for range "domain!$A:$A": "Domain"
    • defined a name for range "domain!$B:$1": "DomainChoice"
  • on sheet "data", I did:
    • enter (from cell A1 downwards) "man", "cat", "dog"
    • enter (from cell B1 downwards) "snake", "lizard"
    • enter (from cell C1 downwards) "frog", "toad"
  • on sheet "animal", I did:
    • in A1, entered the following formula
      =T(INDIRECT("data!R" & ROW() & "C" & DomainChoice; FALSE))
    • filled this formula down to, say, row 50.
    • defined a name for range "animal!$A:$A": "Animal"
    • defined a name for range "animal!$B:$1": "AnimalChoice"
• on the main worksheet, I created two combobox controls:
  • in box 1, I defined the properties ("Format Control...") as follows:
    • "Input range:" - "Domain"
    • "Cell link": - "DomainChoice"
  • in box 2, I defined the properties as follows:
    • "Input range:" - "Animal"
    • "Cell link": - "AnimalChoice"

Now should "mammals", "reptiles", "amphibians" appear in box 1, and the contents of box 2 should change based on the selection.

Look at the various sheets to see what happens behind the scenes. The only requirements are that the order of the values in the "domain" sheet corresponds to the columns on the "data" sheet, and that can only be as many animals as there are rows filled with the formula on the "animal" sheet.

You can hide the three helper worksheets, if you want.

The formula, explained:

T(                // returns an empty string unless the argument is a text value
  INDIRECT(       // returns the value at the given reference string
    "data!R"      // build a dynamic reference string to "data!R1C1"
    &     
    ROW()         // the current row, changes as you fill down the formula
    &
    "C" 
    & 
    DomainChoice  // defined name of "domain!$B:$1", contains box 1 choice
  ; 
    FALSE         // flag to indicate that the formula is in "R1C1" format
  )
)