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.

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," ",""))

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")

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.

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

30 Responses

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.

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.

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).

@Hart, I’m not clear on what you or Kevin are trying to do. If there is a column with the section names, you could filter that column to just show a specific section. Then, the visible rows would only show the results for students in that section.

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.

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:

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.

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?

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.

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.

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.

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.

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……

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

Thanks for showing how to create dependent lists. This is very helpful. Is it possible that every time I re-pick at the first level, the rest of the 2 levels get cleared out. Basically, I am using dependent lists in a form. So when users want to change their option on the first level, the rest of the 2 level should get cleared out so the is not data mismatch.

I’m trying to use Indirect in order to refer to a name in the active workbook which points to a defined name in another workbook, but Indirect won’t allow it (it seems). I can get Indirect to work with a name that points directly to a range in another workbook, however. Given that the “define a name in this workbook as a name in another workbook” method is a way around Excel’s workbook to workbook referencing issues for validation drop down lists and conditional formating, it seems odd.

Does anyone know of a way to have Indirect or some function like it work with a name that points to another name in another workbook?

Hi – I have been studing and trying to figure out how to do the third dependent drop down. I have been to other sites and still can’t figure it out. I get as far as getting the second one to work, but not the third. I am currently using this formula =INDEX(Main,,MATCH(Initiative,Main1,0)) for the second collum. Any thoughts?
Thanks-
Janna

The next column has a drop down menu to choose from cash, check or credit. If credit is chosen, I want it to automatically calculate a 2.75% fee in the next column which will then automatically add into the Grand Total column.

How do I included a blank line in a drop-down box for possible add-ins the the actual drop-down? example… My current drop-down includes | Travel, Meals, Parking, Flight, Lodging. We rarely have anything else. But today someone wanted to add Tolls and Tips, on the fly not added to the list just as a write-in. Please help
Thanks

When you’re setting the Data Validation for the dropdown, notice that there are 3 tabs in that window the last being “Error Alert”. This defaults to the Stop style but you can change it to Information and then the cell will accept any text not just the dropdown list.