Conditional Drop Down Lists in Excel

It’s fairly easy to make one drop down list show items based on what was selected in another drop down. But what about making a third drop down list? And how can you work with spaces, or other characters that can’t be used in Excel names?

Here is a nice, simple conditional list – if you select Red Fruit in cell A2, you can see a list of red fruit in cell B2.

dependentdropdownspace01

Using Named Ranges

There is a named range on the worksheet – RedFruit – and the data validation formula uses the INDIRECT function to create a reference to that range. The formula also removes any spaces in the text, by using the SUBSTITUTE function, because the range names can’t use space characters.

=INDIRECT(SUBSTITUTE(A2," ",""))

dependentdropdownspace02

Lookup Tables for Complex Lists

It’s simple to use the SUBSTITUTE function to get rid of any spaces, but your list of items might contain other characters that can’t be used in range names, such as an ampersand (&) or asterisk (*).

Instead of trying to substitute every illegal character, you can set up a lookup table. Put the list items in the first column, and a short code for each item in the second column. The range with items and codes is named ProductLookup.

The data validation formula will look in that list, to find the code, and show the items from a list with that name. In this example, all the list names end with “List”, so that is added in the formula.

=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")

dependentdropdownillegal06

Add a Third Dependent Drop Down

You could even add a third drop down list that is dependent on the selections in the first two.

dependentdropdownillegal01

Just create a lookup table for each of the secondary lists, and an item list for each of those secondary codes.

dependentdropdownillegal02

Here is the data validation formula in cell C2

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

It finds the product lookup table,

  • VLOOKUP(A2,ProductLookup,2,0)&"Lookup"

the product code in that table,

  • VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)

and adds “List” to create a reference to the list name.

=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

Download the Sample File

You can see the details on my Contextures website, Dependent Lists page, and download the sample file.

_________

9 comments to Conditional Drop Down Lists in Excel

  • Maxim Manuel

    I remember a similar trick on your website 5 years ago. Validation using 2 dependent dropdowns. This technique was the one who made me discover Contextures. I googled Excel validation and it returned me that blog with 2 dependent dropdowns. I was amazed to see that Excel could do this. This is where I learned how to do this.
    Thank you so much for sharing the same technique for 3 dropdowns.

  • Harish Kumar

    Hi Excel Guru, I am trying to prepare two worksheet, First one will act as Input Sheet and another will store the information from input sheet and the user can not insert any content manually in the database sheet. i have got excel template with above criteria (with macro's) from your website, but i am facing one challange that after protecting the database worksheet content i am not able to input the date from Input sheet to database. i request for your valuable input. thanks in advance. Harish

  • Wow - I'm totally lost. I'm looking to use a simple excel drop down that will allow use of multiples of Company, Name, Address, Phone & Email from a list (300+) applied to a separate worksheet page within the same document.

    It needs to auto fill separate cells for each of these entries.

    It is apparent that you have all of these answers and many more, however I don't know where to start looking.

    Thank you for pointing me in the right direction. Lori

  • Kevin

    Hi Debra,

    I was wondering if you have made a tutorial on filtering a list of data. For example, if there is a list of students in different sections, how would I create a dropdown list of sections that will show all of the students in that section along with their work (ie - grades).

    Thanks,

    Kevin.

  • Emily

    Does anyone know how to fix this formula
    =IF(D8=B149,E8*4)*IF(D8=B150,E8*2)*IF(D8=B151,E8)*IF(D8=B152,E8/3)*IF(D8=B153,E8/6)*IF(D8=B154,E8/12)
    Where D8 contains a drop down menu using data validation - drop down menu is displaying payment method-'weekly,fortnightly,monthly,quarterly,semi-annually,annually'- the box E8 has the monetary value of what was paid, the box I'm imputing the data into is to work out the monthly wage..
    Any help would be very much appreciated.

  • Mike

    Can I use this solution for my scenario?

    I have 3 columns of data taken from historical shipping information which I want to use as a transit time guide

    1. Country of destination
    2. Day of despatch
    3. Transit time

    Please advise the best way to show this if the user selects the country then day despatched, so it automatically gives an average transit time.

    I have taken the average transit time from historical data and have 5 results for each destination. (Monday to Friday)

  • Lucas Fogolin

    Hi!

    Look, I've been searching for a solution for months now, but coudn't manage to figure out the ways to do it: I have a list of employees, with the date they were admited in the company and the day they got fired, something like:

    NAME | IN | OUT |
    ======== ============ ============
    Jane | 27/01/2013 | 06/04/2014 |
    Luke | 01/02/2013 | 28/02/2014 |
    Mary | 01/04/2013 | 01/01/2014 |
    ...

    And I have this other sheet:

    SERVICE | DATE | EMPLOYEE |
    ======== ============ ============
    SERV1 | 05/05/2013 |{DROP-DOWN1}|
    SERV2 | 05/04/2014 |{DROP-DOWN2}|
    ...

    And I'd like to lock the drop-down menu according to the date a employee was working, so, the {DROP-DOWN1} would have all 3 names (Jane, Luke and Mary), 'cause they all were working in the company on 05/05/2013, but {DROP-DOWN2} would only have Jan as an option, 'cause seh would be the only one still working on the comppany in that period.

    The conditional list would, somehow, have to admit some sort of condition like IF(AND([B2:B4]">=" & SERVICE_DATE;[C2:C4] "<=" & SERVICE_DATE);TRUE;FALSE), thus generating a whole new list where all the items are true to the conditions set.

    How can I do this?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>