Search code examples
excelkeydropdown

Excel dropdown list dependent on other dropdown list which has the "key" to get the values

I have three sheets, Crops, Varieties and Report as shown in the images belowCrops SheetVarieties SheetReport Sheet

In Report sheet the user can select the Crop (D1), which is a dropdown list with values taken from the Crops sheet.

Crop Dropdown List

So I need the Variety dropdown list take its values from the Varieties sheet, taking the crop(D1) as a “key” (sheet Varieties, column A) to fill the list with the corresponding variety values (Sheet Varieties, column B) as shown in the image below Varieties Dropdown List

Looking for a solution, I considered the INDEX with MATCH functions, as well as VLOOKUP function but none of these does exactly what I need.

Any Idea please? I would like not to use VBA but maybe is the only way?

Solution

  • The key thing to understand here is that for a dropdown list in Excel, the values have to appear in the sheet (or be hard-coded in the data validation source). You can't supply, say, an array formula as the data validation source.

    This screenshot (explained below) shows one way to achieve what you're after (please excuse non-existent flowers):

    Excel screenshot

    Here's what's happening in this sheet:

    Crop/Variety - these are just values, entered as in your first screenshot above.

    Unique crops - this uses the formula =UNIQUE(A2:A8) in cell E2. This selects all the distinct crop names from column A.

    Crop (row 11) - this has its data validation list set to the Unique crops list created above - something like E2:E8 (see note 2 below).

    Varieties - this uses the formula =FILTER(B2:B8, A2:A8=E11, "") in cell H2, where E11 is the selection from your Crop dropdown list.

    Variety (row 13) - this has its data validation list set to the Varieties list created above - something like H2:H8 (see note 2 below).

    You can see from the screenshot that this allows the list of selectable varieties to be updated dynamically.

    Some notes:

    1. When you update the Crop selection, your Variety selection will remain unchanged (even though the selected variety will no longer be in the list). There are things you can do about this (and articles online) but it's outside the scope of the question.

    2. The source ranges (E2:E8 and H2:H8) need to be large enough to include all possible values. This especially applies to H2:H8 where the contents will change dynamically. In practice, what this means is that your Variety dropdown list might have several blanks at the bottom. This is fixable (most easily by using a formula with OFFSET() to change the length of the list) - again, there are articles online explaining how, but this is outside the scope of the question.

    3. You will need dynamic arrays to be enabled. This is the default, unless you are using a very old version of Excel (in which case the UNIQUE() and FILTER() functions might also not be available).

    (If you need assistance with the points in the notes, and can't find what you need online, please ask another question.)

    There is no VBA involved here.

    Hopefully this achieves what you are after?

    Update for old versions of Excel (without UNIQUE() and FILTER()):

    It is possible to extract the distinct values from a column without the newer Excel functions UNIQUE() and FILTER(), although it's a little messier.

    1. Put your list with duplicates in column A - say, A1:A9 (for the purposes of this example).
    2. Put this formula in cell B1 and copy down: =IF(COUNTIF(A$1:A1,A1)=1,A1,"")
    3. Put this formula in cell C1 and copy down: =IFERROR(SMALL(IF($B$1:$B$13<>"",ROW($B$1:$B$13)),ROW(1:1)), "")
    4. Put this formula in cell D1 and copy down: =IF(ISNUMBER(C1), INDEX($B$1:$B$13, C1), "").

    Example result:

    Excel screenshot