Different Excel Drop Downs from One Source

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.

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, while the Product drop down would have =ProdList as its source.

APJ_UnivDD00

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.

APJ_UnivDD02

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.

APJ_UnivDD01

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.

APJ_UnivDD03

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?

_________________________

You may also like...

20 Responses

  1. Roger Govier says:

    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

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

  3. sam says:

    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

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

  5. Roger Govier says:

    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.

  6. Lincoln says:

    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.

  7. david baron says:

    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

  8. Darren Bartrup says:

    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.

  9. shirley says:

    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?

  10. Roger Govier says:

    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

  11. Steve says:

    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

  12. Roger Govier says:

    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

  13. Juan says:

    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

  14. Juan says:

    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

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

  16. Juan says:

    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!

  17. Usha says:

    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

  18. Zaigham says:

    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.

  19. Mike Duffy says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *