Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

List Drop-Down Menus

Latest post Wed, Jan 7 2015 8:34 AM by Omar. 5 replies.
  • Fri, May 9 2014 6:50 AM

    • GenDemo
    • Top 50 Contributor
    • Joined on Mon, Mar 3 2014
    • South Africa
    • Posts 19
    • Points 207

    List Drop-Down Menus

    Hi Guys

    Do any you know how one can create a list drop-down in a cell where you can type in any value, but still have the option to select something from the drop-down list?

    • Post Points: 21
  • Fri, May 9 2014 7:59 AM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Go into Data Validation, then the Error Alert tab. Uncheck the "show error alert" box.

    Omar Freeman Kitchener, ON

    • Post Points: 21
  • Fri, May 9 2014 9:18 AM In reply to

    • GenDemo
    • Top 50 Contributor
    • Joined on Mon, Mar 3 2014
    • South Africa
    • Posts 19
    • Points 207

    As simple as that...

    awesome! thnx!

    • Post Points: 21
  • Wed, Dec 24 2014 7:14 AM In reply to

    Dependent dropdown list is something that keeps-on changing its items based on a change in the value of another dropdown. 

     

    Execute the following steps to create these dependent dropdown lists.

     

    1 Name the range A1:A3 as Food.

    2 Name the range B1:B4 as Fruits.

    3 Name the range C1:C4 as Vegetables.

    4 Name the range D1:D3 as Meat.

    5 Select cell D3 and go to Data Validation menu in the Data tab and click on Data Validation.

    6 In the Data Validation window, select “List” from the dropdown.

    7 In the source section, type “=Food” and click on OK button.

    8 Select the cell G3 and go to Data Validation menu in the Data tab and click on Data Validation and repeat step 6.

    In the source section, type “=INDIRECT(D3)” and click on OK button.

    excel vba programmer

    • Post Points: 21
  • Wed, Jan 7 2015 12:59 AM In reply to

    • GenDemo
    • Top 50 Contributor
    • Joined on Mon, Mar 3 2014
    • South Africa
    • Posts 19
    • Points 207

    @krishnap2w2 i dont know what you are trying to illustrate with your example, but I am very curious. However, when I try to do this, the Data Validation for cell G3, gives an error "The Source currently evaluates to an error. Do you want to continue?" with a "yes" and "no" option. If i click "no", it just goes back to the data validation window; while if I click "yes" it goes back to the spreadsheet, but the drop down menu in G3 has nothing in it/doesn't work. Did I miss something in your steps?

    • Post Points: 21
  • Wed, Jan 7 2015 8:34 AM In reply to

    • Omar
    • Top 10 Contributor
    • Joined on Sat, Jan 12 2008
    • Kitchener, ON
    • Posts 217
    • Points 2,949

    Step five needs a different cell location. Try F3. Then also put that location into the Indirect function.

    This example is intended to show how you can have different lists show up depending on your main choice. So F3 would have a drop down of food groups, and G3 would have a drop down of foods within the chosen group.

    In my work, I could have a list of stores in the main group, and then be able to choose from a group of salespeople based on the store that I chose.

    edit:

    Oh yes, F3 can't be blank when you are setting up G3 or you will get that error.

    Omar Freeman Kitchener, ON

    • Post Points: 5
Page 1 of 1 (6 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.