Show Different Excel Drop Down Lists in Cell

22
8



Go to to get the sample workbook
Select an item from an Excel drop down list, and see only the related items in the next drop down list. In this example, select Fruit from the first drop down, and only fruit names appear in the next column’s drop down list.

Instructor: Debra Dalgleish, Contextures Inc.

Get Debra’s weekly Excel tips:

More Excel Tips and Tutorials:

Video Timeline:
00:00 Intro
00:39 Set Up the Worksheets
01:15 One-Word Names
01:41 Create Tables
02:55 Name the List Items
04:15 Main Drop Down List
05:48 Dependent Drop Down Lists
07:39 Get the Sample File

Video Transcript (abridged)

In this video, you’ll see how to create two dropdown lists. The first one, for produce type, fruit or vegetable. In the next column, there’s a dependent dropdown list, it will change based on what you selected in the other column.

Here it shows a list of vegetables. If I go to next row and select fruit, Column C shows list of fruit.

Here’s a new workbook where we’re going to set up dropdown lists. We’ve got 2 sheets. One is Data Entry, other one is called Lists. Start a new blank workbook and name the 2 sheets.

I typed Produce Type and Item on this sheet, where we’re going to create dropdown lists.

On the other sheet, I’ve got 3 lists set up, Produce List with Fruit and Vegetable, and a fruit list, and vegetable list, with 4 vegetables.

The produce list is our main list. We have 2 items, and it’s important to use a one-word name in this list for each produce type. We’ll those names later, and Excel won’t allow spaces.

Each list will be changed to a formatted Excel table. In the first one, click anywhere in the list. On the Home tab, go to Format as Table, select a format that you like.

My list is B2 to B4, and has a header. Click OK, and there’s our formatted list. It has a dropdown list in the header cell, to filter or sort, and a corner tab here to change the size of this table. If we add new items, the table automatically expands to include them.

We’ll do the same thing for the fruit and vegetable list. Click anywhere in this list, Format as Table. Select a color. Again, it has headers. And same for vegetable, green, has header.

The next step is name cells with items in each list. Select cells B3 and B4, with produce type names, and to name these 2 cells, click the Name Box. Type a one-word name, Produce, then press Enter, to complete the name.

It still shows Table1, but click the arrow to see Produce. Give the fruit list a name, it has to be an exact match for the item in this produce list. Select these 4 cells, click in Name Box, type fruit, press Enter. Upper or lower case doesn’t matter, as long as spelling is the same.

Now we have a fruit list, do the same here, this will be called Vegetable, press enter. Now we can see all 3 names, Fruit, Produce, and Vegetable, and table names.

Next, set up the data entry sheet. I’ve got headings entered, and I’ll create a table from these 2 cells, then add items in rows below.

With a heading cell selected, on Home tab, Format as Table, select style you like. Again, my table has headers, OK. It creates a table with one blank row below headings.

We’ll set up a very simple dropdown in this cell, where we can select a produce types, fruit or vegetable.

Go to Data tab, click data validation, on Settings tab, select List.

For source, we’ll use our produce list. On a Windows computer, press F3 key, to see names you’ve created. Click Produce, OK, it puts in an = and the name of that range.

If you don’t have that shortcut, type =produce, click OK.

Now we’ve got a dropdown arrow when this cell is selected, select fruit or vegetable.

In this cell, we’ll create dropdown that depends on what we selected. If Fruit is selected, it will show fruit list, if we chose Vegetable, we’ll only see vegetables.

To create this dropdown, on Data tab, click Data Validation. We want a list, and this time, we’ll use a formula.

Type =. The function we’re going to use in this formula is INDIRECT, then click an open bracket.

We want to use whatever name has been selected, so type B3, close that bracket.

Instead of typing fruit or vegetable, we’ll use INDIRECT function to get name that we want to use from cell B3, and create a reference to that list, click OK.
It will create a list based on that produce type. Select lemon. Go to next row and type fruit again, it shows fruit list.

Change to vegetable, it shows list of vegetables. So this is dependent dropdown list.

We create a main dropdown, then a dependent dropdown- items in that list depend on cell we told it to look at.

Go to to get the sample workbook

Nguồn: https://dantrionline.com.vn

Xem thêm bài viết khác: https://dantrionline.com.vn/lam-dep/

22 COMMENTS

  1. In the latest Excel this will not work if you convert each of your lists in the List page into a list; I was going nuts trying to figure out what I was doing wrong, then I saw another video, and in that one he just selected the ranges and gave them names without turning the original lists into actual lists and it worked.

  2. WHY IS MY INDIRECT FORMULA FUNCTION NOT WORKING IM TYPING =INDIRECT(CELL VALUE) AND I GET ERROR "THE SOURCE CURRENTLY EVALUATES TO AN ERROR. DO YOU WANT TO CONTINUE"

  3. Hi, are you going to do an update for the Excel Data Validation Combo box using named ranges? i have some problems when the named range is a table-based one. Thank YOu.

  4. I like your explanation, Your shared information need to me , I search in youtube for how to create this option, finally i got a solution through your video thank you.

  5. Great video ,i realised that if you had selected fruit or vegetable and i change my produce list the previous entry on the item remains until i select the drop down box ,how can i create an error message if the produce type is changed ??

  6. Is their a way to make a drop down that changes another drop down at the same time

    For example:

    Carrots = $15
    but if i change the item to Pizza
    it changes the price and the item at the same time
    pizza=$8

  7. This video is great! I didn't know you could do this… I knew about single drop down list, but not dependent drop lists. Not only did I learn something new, the way the video was done is really great. perfect pace, perfect level of detailed explanation. I know a lot about Excel, but even a new bee could follow along and get it right. GREAT JOB!!

LEAVE A REPLY

Please enter your comment!
Please enter your name here