To help users enter data in a spreadsheet, you can create drop down lists with Excel’s Data Validation feature. For example, in an order form, you could provide drop down lists of customers, products, colours, sizes and shipping methods.
Data Validation Source
Usually, each of these lists would need a different Source in the Data Validation dialog box.
- The Customer list would have =CustList as its source
- The Product drop down would have =ProdList as its source.
Use the Same Source
Instead of using a different source for each data validation list, AlexJ has devised a simple way to use the same source for all the lists. This makes it much easier to create and maintain a set of drop down lists.
In AlexJ’s sample file, he’s recording farm information, with drop down lists for Fruit, Vegetable, Farm Equipment and Farmer. He’s typed these lists in the workbook, and named them: DD.Fruit, DD.Veg, DD.Equip and noDD.
The noDD list is just a blank cell, and it can be used when you want users to be able to type freeform in a column.
In row 2, above the table where users will select from the drop down lists, AlexJ has typed the name of the source range for the column below.
Then, AlexJ selected all the blue cells, where drop down lists will be created. In the Data Validation dialog box, he selected Allow: List. As the Source, he entered: =INDIRECT(C$2)
The column reference (C) is relative, and the row reference ($2) is absolute.
Setup Tips
- AlexJ hides row 2, using Outlining, so users aren’t distracted by the range names.
- In the sample file, the named ranges are on the same sheet as the data entry range. In his actual files, AlexJ would have these on another sheet, hidden from users.
- Instead of selecting noDD, cell F2 could be left blank, so no dropdown list would appear.
- No Error Alerts or Input Messages are used in the sample file, but you could add these to your application, if needed.
- The drop down range names in cells J6:M6 are in a range named DD.Ranges. That range is used to create the drop down lists in row 2.
Download the Sample File
To download AlexJ’s sample file click here: Universal Data Validation Drop Downs (zipped 25 KB)
What Do You Think?
I frequently use the INDIRECT function to create dependent data validation lists. However, I hadn’t seen this idea used before, to create different drop down lists from the same source formula.
To me, it seems like a great way to create several adjacent lists, and makes it easy to maintain them.
AlexJ would appreciate your feedback. What do you think? Would you use this technique? Anything you’d add or change?
_________________________
Hi Deb
I avoid the use of the voltaile Indirect() function wherever I can.
The exception is whenever I need to use dependent dropdowns, as per your method.
To make life easy and not even have to worry about naming each list, I prefer to use the following method. I will describe it relative to the data on AlexJ’s sheet.
Create a Named range called Lists =’Sheet1!$J$7:$M$10 (in reality, this would be on a separate sheet and would be a dynamic range)
Create a second Named range called UseList as
=INDEX(Lists,,COLUMN(‘Sheet1’!A$1))
Select C7:F13>Data Validation>List>UseList
Hi Roger,
Good point about the volatile functions, so you wouldn’t want to use INDIRECT in a large area, or a calculation heavy workbook.
Your approach is interesting too, thanks for describing it.
There would be blank cells in any lists that are shorter than the others though, unless I’ve done something wrong in testing.
Your technique also assumes that the lists in the data entry area are in the same order as the lists in the lookup area. Perhaps we could combine the techniques. Instead of creating a define name UserList, you could put lookup column numbers in cells C2:F2. Then, for the data validation formula: =INDEX(Lists,,C$2)
1 Dont Use Indirect, use Evaluate(CellRef)
2 For Dependent Drop Downs
A1 – Has A List of Clients
B1 – To have Corresponding Cities
Define a Name CORR_CITY as
Index(City,Match(A1,Client,0):Index(City,Match(A1,Client,1))
If Clients are Sorted are sorted in Ascending Order
Else
Index(City,Match(A1,Client,0):Index(City,Match(A1,Client,0)+Sumproduct((Client=A1)*1)-1)
Name City
ABB AA
ABB CC
ABB DD
BHEL AA
BHEL DD
BHEL ZZ
TVS AD
TVS EE
Thanks Sam. It’s interesting to see all the different methods that people use.
I’ve got instructions for Dependent Data Validation From a Sorted List, similar to what you described, on my website.
Hi Debra
Thanks for putting the file up on your site where i describe more fully my method of dealing with DV Ranges.
http://www.contextures.com/RJG_Universal_DD.zip
I should have added in the description, that I would normally hide the sheet called Lists.
Sam
I’m using Excel 2002, and don’t seem to have the Evaluate() function you quote. Any ideas where I might find it?
btw Debra
Thanks for the post. It’s immediately helpful for me.
im using excel and on the data validation list i want it to correspond with another drop down list. ie i click on an option on the drop down list and automaticly it selcts another option on another list?
help will be muchly appreceated
Lincoln – sorry, only a few months after the original post.
I think Evaluate() is a left over from early Excel versions. You can’t use it as a worksheet function, but can use it within a named range.
If you enter a function or calculation without the = sign within a cell (so the function is shown as text) then evaluate will, well, evaluate it for you.
How can I use one list on a seperate sheet in the workbook. This data will be used across several other sheets in the workbook. I would rather have all my data on one sheet instead of multi sheets. Is this possible?
Shirley
The method described in this article, can have the lists all on one sheet,
Take a look at
http://www.contextures.com/RJG_Universal_DD.zip
or
http://www.contextures.com/RJG_Universal_DD_Dependent.zip
Is there a way to use a drop down list with the items in the list having drop downs them selves?
Original List
Base
EXE
INT——Drop down from this list item
BR1
BR2
BR3
I have a billing sheet I am trying to idiot proof
Thanks for your thoughts
Hi Steve
That is exactly what Dependent Dropdowns achieves.
Try download in the file
http://www.contextures.com/RJG_Universal_DD_Dependent.zip
and take a look at the tutorial at
http://www.contextures.com/xlDataVal15.html
Dear Debra;
Thanks for your website and for your YouTube channel. I have looked in your help files and have not find solution to my problem. Perhaps I’ve grown blind with my desperation. 🙂
I would like to make three data validation (DV) selections. DV1 would allow selection from a list, DV2 would =indirect from DV1, and DV3 would also be an =indirect from DV1. I know that my logic doesn’t work, for there would be two X’s to one Y, per say.
Here’s my sample data:
Equipment Description Use
Incinerator Incin.of Solid Waste Afterburner
Incin.of Odor Fume
Incin.ofparticulate Liquid
Oxidizer
Heat Recovery Air to Air Use1
Fluegas to air Use2
Liquid to liquid Use3
Use4
DV1 would allow you to select from: Incinerator or Heat Recovery
DV2 based on DV1 would show the full description menu
DV3 based on DV1 would show the full use menu
Based on some of the lessons I read, I am able to do the first DV’s, the last one is beyond my understanding. I don’t know if its an order of data table or renaming the lists. I’ve tried naming the list in the USE column as IncineratorUSe, and HeatRecoveryUse respectively, and nothing.
I have reviewed the Universal DD Dependent tutorial, but found out that I need to make a column for each combination and think that there might be an easier way of doing this.
Can you please help, any guidance would be greatly appriciated. I’m a novice to programing.
Thank you and all those who may reply.
-Juan
I noticed that the data got moved around. Here’s my second attempt on submitting it.
Equipment Description Use
INCINERATOR INCIN OF SOLID WASTE Afterburner
INCIN.OF HYDROCARBON Fume
INCIN.OF LIQ.WASTES Liquid
INCIN.OF ODOR Oxidizer
INCIN.OF PARTICULATE
HEAT RECOVERY AIR TO AIR Use1
COMPR.HOT GAS TO LIQ Use2
FLUE GAS TO AIR Use3
FLUE GAS TO FLUE GAS Use4
FLUE GAS TO LIQUID Use5
FLUE GAS TO POLUTANT
Juan, create 5 lists with these names:
Equipment, IncineratorDesc, HeatRecoveryDesc, IncineratorUse, HeatRecoveryUse
For the first data validation dropdown, use the Equipment list.
For the Description column, use a list with this formula:
=INDIRECT(SUBSTITUTE(A2,” “,””)&”Desc”)
For the Use column, use a list with this formula:
=INDIRECT(SUBSTITUTE(A2,” “,””)&”Use”)
Debra,
This worked wonderfully. In the interim I had used the Concatenate function to append a 2 instead of the USE you presented. This was because I had named one list i.e. HeatRecovery, and HeatRecovery2. I used the concatenate output as the indirect source inorder to display the HeatRecovery2 menu. It involved about three more steps, you’re solution is straight forward.
Let me know how to upload my excel sheet for availability to others. You and your site have helped me enormously.
Thanks a billion (in todays dollars) hahaha!
Hi Roger,
Thanks for sharing the information.
However, I am using the link – http://www.contextures.com/RJG_Universal_DD_Dependent.zip
The Counter(formula set up in cell ‘B2’) and Use List formula (also set up in cell ‘B2’) is giving me error message – ‘Circular reference error’.Why is that so ?
Regards,
Usha
I’m not sure that at this belated stage (i.e., after 33 months) my comments get some attention or not. However, to avoid hiding the ROW 2, I have just changed the formula from =INDIRECT(C$2) to =INDIRECT(J$6) in Data Validation and deleted the ROW 2 and it works fine, no error was seen.
How would I have 2 separate dropdowns from 2 separate lists,one place above the other and the result from both fill the same cell below. If the cell below is filled with the result from the first dropdown its needs to be deleted and filled with result from other dropdown and vicversa.
Mike, you would need a bit of programming that runs automatically when you make a change in one of the drop down list. For example, if the drop down lists are in cells D3 and D4:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$D$3” Or Target.Address = “$D$4” Then
Range(“D5”).Value = Target.Value
End If
End Sub