Excel: dropdown list based on key value from other sheet

Question

I have been struggling with this requirement for some time

I have two excel sheets as follow

Sheet1 (main sheet)

Fruit   |  Fruit Type
---------------------
apple   |  ??????  <----- should have drop down list based on key value from Sheet2
apple   |  ??????  <----- should have drop down list based on key value from Sheet2
banana  |  ??????  <----- should have drop down list based on key value from Sheet2
apple   |  ??????  <----- should have drop down list based on key value from Sheet2
banana  |  ??????  <----- should have drop down list based on key value from Sheet2

Sheet2 (fruits types)

Key     |  Value
---------------------
apple   |  Red Apple
apple   |  Organic Apple
apple   |  Green Apple
banana  |  African Banana
banana  |  Yemen's Banana

I want a drop down list in sheets1 (fruittype column) which should be based on Sheet2 key values

I know that drop down lists could be done easily by using the "data validation"

but the difficult part is how to make this drop down list shows only the data based on the key values

VLOOKUP is in your tags - it seems VLOOKUP is the answer for this. If not please explain further — Nick.McDermaid
VLOOKUP is no use. OP wants to populate a drop-down list of valid values, not copy a single value. I do not think there is a solution for this outside VBA. — MattClarke

Tim Williams Tim Williams · Accepted Answer · 2014-10-30T04:59:31

You need to use VBA for this - here's one approach which can work (though I'm not sure it's the best...)

1.Add a function like this to a regular module

  Function GetFruitList()
    Dim m, f, rngList, rng As Range
    Set rngList = Sheet2.Range("A:A")

    f = Application.Caller.Offset(0, -1).Value  'what fruit ?
    m = Application.Match(f, rngList, 0)        'is fruit in list ?
    If Not IsError(m) Then
        Set GetFruitList = rngList.Cells(m).Resize( _
                  Application.CountIf(rngList, f), 1).Offset(0, 1)
    End If
  End Function

2.Add a name "FruitList" to your workbook in "refersTo" put =GetFruitList() (Formulas tab >> Name Manager)

3.Set up data validation on the input cells, using the "Allow >> List" option and =FruitList for "Source"

This assumes your lookup list is on Sheet2 (I'm using the sheet codename in my example but you can use the tab name instead) and that the list is sorted on the first column.

It works (at least it does for me!) because the function GetFruitList gets called in the context of the specific cell which is being clicked in - so we can use Application.Caller to get that, and then offset one cell to the left to find out which category item we need to return values for.

Excel: dropdown list based on key value from other sheet

1 Answers