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.

_________

17 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?

  • Hello Debra..

    Thanks for guidance. I was able to complete my task by simply following your instruction regarding the conditional dropdown list.
    Further to your item on "Block Changes in First Drop Down", can you further extend it to show on the coding formula on how to block changes in the second drop down?

  • Roopali

    Hi,

    I have an excel sheet in which I have 2 drop downs. The second drop down is dependent on the first one.
    The problem is that when I change the value in the first drop down, it updates the list in the second dd but it does not clear out the existing value on its own.

    Is there a way to clear out the second dd value the moment I change the value on the first dd.

    Thanks!

  • Hi

    This is fantastic, a great way to deal with illegal characters. I am wondering if it's possible to extend it to a 4th level - so let's say in your example, to include a type (e.g. Apple Crisp = Pastry, Apple Pie = Pie). How would you go about doing this?

    Would it be a case of having a new lookup table for the type, and inserting another nested INDIRECT/VLOOKUP to do this? If you're able to shed some light on this, that would be great.

    Cheers

    Joe

  • Aniket

    Hi, I have a table of projects where each project has 2 identifiers. I have followed your instructions to create a dependent drop down for the 2 project identifiers, but I would like my 3rd drop down to be dynamic and pull up a list of projects from a master project tab based on the 2 identifiers. The project list will change on a frequent basis, so I'm looking for functionality where I can regularly update the project table, but have the Project Drop Down list to change accordingly.

    Basically I'm trying to avoid having team members scroll through an entire project list in a drop down, and need the 2 project identifiers to help minimize the scrolling.

    Thanks in advance!
    Aniket

  • David

    Hello,

    I am working on a project where I would like to be able to filter a drop down menu to display a limited number of records from a total. For example, I have a spreadsheet which includes a list of names, along with each persons personal and unique information (phone number, home address etc.) as well as work department, position and what councils or groups they are part of.

    To start, Drop Down A allows the user to select from the list of names. When a name is selected all information related to that individual is entered into the fields on the display page. No issue with this part.

    Drop Down B and C allows the user to select a department and a position. I would like to have these selections filter Drop Down A. Example, I want to know which individuals are Technologists in the R&D department. I really have no idea how to do this effectively.

    Lastly I have multiple columns each associated with a different council or group. For an individual there will be a Boolean indicator in each column indicating if they are part of that group or not. I would like to create a fourth drop down (D) that will also Filter the options available in Drop Down A. I am even more lost on this issue than the one above.

    Any assistance would be very much appreciated.

    Regards, Dave

  • Bijay

    heloo excel experts i am going to ask you one question .please let me know with full step
    I have more datas like items last price initial price customer name phone no qty s.amount remarks
    apple 200 300 aryan 984235435 1 500 profit
    orange 100 200 devid 985362451 2 400 profit

    like that and i have to add these datas regularly (day by day)so i want to make a software like advanced filter which you have shown but the condition is that i would like to put a file limitation so that it is easy for me upto which date the software works......

  • Bijay

    dear sir i have one another question for you
    like i have a sheet in which i have apply formulas there
    like =sum(a3:g3) =sum(b3:g3) =sum(c3:g3) like that....sir i want to do like that when i enter that cell that display formula right and when we dont write anything that cell shows 0 sir i wana do like that when i am entering in that cell that doesnot show formula but we can edit ,insert,delete everything we can do in that cell and also when we open the file it doesnot show any value like another sheet....like a software.......is it possible if so how can we do it...by vb or directly ..sir give me the full process with all og the informations

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>