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.
Video: Make a Basic Conditional Drop Down List
To see how to make a basic dependent drop down list in a cell, watch the steps in this short video, and the written instructions are on the Excel Dependent Drop Down List page on my Contextures site.
Using Named Ranges
Sometimes things aren’t quite as simple, as you can see in the next example.
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.
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.
_________
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.
Thank you for pointing me in the right direction. Lori
Lori, there is a sample order form on my website that might help you get started:
http://www.contextures.com/xlOrderForm01.html
You can select a product, and it fills in the price.
Or, select a customer, and if fills in the address, city and state.
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.
wondering the same thing…..if you’ve got this figured out already, please kindly let me know.
@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.
Emily,
=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),0))))))
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)
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?
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!
@Roopali, There is an example on my site that uses programming to clear out the second value, when you select from the first drop down.
On my Excel Samples page, go to the data validation section, and look for DV0064 – Dependent Lists Clear Cells
http://www.contextures.com/excelfiles.html#DataVal
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
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
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
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……
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
Hello,
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.
Hello,
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?
Thank You,
Dave
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
I am stumped! I am trying to figure out how to calculate a %fee based on a word in the drop down menu.
Example:
Wine Purchases + Silent Auction Purchases + Live Auction Purchases = Subtotal
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.
I want to get away from using named ranges for cascading dropdowns, as they are finicky to maintain.
What I would rather do is fill the dropdown box from a sheet showing matches. Example.
Conifer | Pine | Lodgepole Pine
Conifer | Pine | Ponderosa Pine
Conifer | Pine | Scots Pine
Conifer | Fir | Balsam Fir
Conifer | Fir | Veitch Fir
Conifer | Fir | Alpine Fir
Conifer | Spruce | Black Spruce
Conifer | Spruce | Colorado Spruce
Conifer | Spruce | Norway Spruce
Fruit | Apple | Battleford Apple
Fruit....
Then I can type C it fills in Conifer tab and shows a choice of Pine, Fir Spruce, I type P it matches Pine, and shows the three species of pine.
I want to do this without mousing about.
Is this possible?
(I am running Excel for Mac 2011)
Sherwood did you had any reply? im l;ooking for the same solution
Here’s a guest post I wrote some time back with an approach that doesn’t require you to set up individual Named Ranges:
http://chandoo.org/wp/2014/02/25/robust-dynamic-cascading-dropdowns-without-vba/
I’m also working on a nifty commercial add-in that lets you do a wildcard search of large data validation lists. Probably about a month away from finishing it, though.
Good evening,
Brilliant information above but I have one question how do you add a 4th dependant drop drown please?
Many tahnks
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.
Hi can you try to check my formula if it’s correct?
=if(r2=””,MarketGroup,indirect(“FakeRange”))
I am trying to block the first drop down when there’s already a value entered in the second drop down.