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?
_________________________
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.