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