Choose Fruit in one cell, and the next cell should offer Apple, Pear, and Plum. Choose Drinks, and it should offer Coffee and Tea instead. That's a dependent drop-down list: the first selection controls the choices in the second.

To make the lists dynamic, keep the source items in an Excel Table. In Microsoft 365 or Excel 2021 and later, you can use FILTER, UNIQUE, and SORT to build the choices in helper cells, then give those ranges names for Data Validation. The Table reference includes new source records, so you don't need to edit the helper formulas when you add an item.

The example below uses three independent Category/Item pairs. The older INDEX/MATCH method from the original tutorial follows afterward for workbooks without those dynamic-array functions. Excel 2019 and 2016 do not support the modern formulas shown here.

Download the dependent dropdown example workbook to follow along with the Source, Entry, and Lists sheets. It starts with the seven source records and three sample selections shown below; add Fruit/Mango to try the expanding list.

Set up the source Table and input cells

Create three worksheets named Source, Entry, and Lists. Put this data in Source!A1:B8, including the headings:

Category	Item
Fruit	Apple
Fruit	Pear
Fruit	Plum
Drinks	Tea
Drinks	Coffee
Office	Notebook
Office	Pen

Convert the range to an Excel Table with headers and name it tblItems. Each row holds one category/item pair. Fruit has three items; Drinks and Office have two each. There's no need to pad the shorter lists with blank records. If Tables are unfamiliar, the Table Design in Excel walkthrough explains the difference between a range that looks like a table and an actual Excel Table; its screenshots show the historical interface.

On Entry, put Category in B2 and Item in C2. We'll use B3:C5 for three rows of selections. Keep these as ordinary worksheet cells for this example.

The source Table and the cells people fill in have different jobs. As I noted in the original tutorial, I normally put the reference Table on a different worksheet. Here, Source holds the records, Entry holds the selections, and Lists keeps the helper formulas out of the way.

The dropdown interactions and screenshots below were checked in Excel for Mac 16.114.

In Lists!G3, enter:

=SORT(UNIQUE(tblItems[Category]))

Formula

UNIQUE takes one copy of each category, and SORT puts them in ascending order. You should see Drinks, Fruit, Office in G3:G5. The formula occupies G3; its results spill into the cells below.

Keep the helpers in ordinary cells outside any Table. Excel Tables hold the source records, but they cannot contain these spilling formulas. Microsoft's guide to spilled array behavior explains that distinction. Leave the cells below G3 empty so the list has room to grow.

Name the spill range

Create a defined name called CategoryOptions, with Workbook scope, that refers to:

=Lists!$G$3#

Formula

The # means the whole spill range beginning at G3. Don't name only the current three cells: the spill reference is what lets the name follow a longer or shorter list.

Use the naming controls on Excel's Formulas tab. In Windows, Define Name opens the dialog where you enter the name, choose Workbook for Scope, and enter the reference in Refers to. Microsoft documents these fields in Names in formulas. In the Mac version used here, open Formulas > Define Name to create the name and enter its reference.

Apply the category validation to all three rows

Select Entry!B3:B5, open Data > Data Validation, and choose List under Allow. Use this Source:

=CategoryOptions

Formula

Keep In-cell dropdown enabled, then confirm the rule. Each selected cell should offer Drinks, Fruit, and Office. For a closer look at the dialog, see how to create a drop-down list with Data Validation.

Build a separate item list for each input row

First choose Fruit in Entry!B3. In Lists!H3, enter:

=SORT(UNIQUE(FILTER(tblItems[Item],(tblItems[Category]=Entry!$B$3)*(tblItems[Item]<>""),"")))

Formula

FILTER keeps items whose category matches B3 and whose item text isn't blank. The * joins those two conditions: both must be true. UNIQUE removes repeated items within that category, and SORT orders the result. With Fruit selected, H3:H5 should contain Apple, Pear, Plum.

The final "" is FILTER's result when nothing matches. With this source data, leaving the parent category blank produces blank text in the helper cell. It does not create a spill range with zero cells. Choose the category first, then use its item list.

For the next input row, put this formula in Lists!J3:

=SORT(UNIQUE(FILTER(tblItems[Item],(tblItems[Category]=Entry!$B$4)*(tblItems[Item]<>""),"")))

Formula

For the third row, put this formula in Lists!L3:

=SORT(UNIQUE(FILTER(tblItems[Item],(tblItems[Category]=Entry!$B$5)*(tblItems[Item]<>""),"")))

Formula

Notice that the helpers start in different columns, H, J, and L. Don't put them in H3, H4, and H5: the first list would run into the next formula as it spilled downward. Keep the area below each helper clear.

Connect each item cell to its own list

Create these three workbook-scoped names:

Name	Refers to	Parent selection
ItemOptions3	`=Lists!$H$3#`	Entry!B3
ItemOptions4	`=Lists!$J$3#`	Entry!B4
ItemOptions5	`=Lists!$L$3#`	Entry!B5

Now apply List validation to each item cell separately, using the matching name in Source:

Item cell	Data Validation Source
Entry!C3	`=ItemOptions3`
Entry!C4	`=ItemOptions4`
Entry!C5	`=ItemOptions5`

The validation rule points to a named range of cells. The longer FILTER formula belongs in the helper cell, not in the Data Validation Source box.

Excel Data Validation dialog with Allow set to List and Source set to =ItemOptions3 — The item rule uses =ItemOptions3 as its source, with In-cell dropdown enabled.

Choose Fruit in B3, Drinks in B4, and Office in B5. Open each adjacent item dropdown and check its choices:

Category	Item choices, in order
Fruit	Apple, Pear, Plum
Drinks	Coffee, Tea
Office	Notebook, Pen

The shorter lists should have two choices, without an extra blank row to match Fruit's length. Changing B4 should change C4's available choices while leaving the other rows' lists alone.

Excel Entry sheet with Fruit and Apple, Drinks and Coffee, and Office and Notebook in three Category and Item rows — Three rows of selections: Fruit/Apple, Drinks/Coffee, and Office/Notebook. C3 is selected with its dropdown control visible.

Add a new record at the bottom of tblItems: Fruit in Category and Mango in Item. Confirm that the Table includes the new row. With Fruit still selected in B3, the helper in H3 should now show Apple, Mango, Pear, Plum.

If the helper or dropdown still shows the old choices after adding an item or changing a category, use Excel's Calculate Now command, then reopen the dropdown. This is necessary when calculation is set to Manual.

Then open the dropdown in C3 and select Mango. Checking the formula result alone isn't enough; the validation rule must also refer to the expanding spill. The Drinks row should still offer only Coffee and Tea. Microsoft likewise recommends testing the resulting rule in its Data Validation instructions.

Open Excel item dropdown for Fruit showing Apple, Mango, Pear, and Plum — With Fruit selected, the open item list offers Apple, Mango, Pear, and Plum.

Adding source records and adding input rows are separate tasks. To add another Category/Item pair on Entry, give its category cell the =CategoryOptions rule, create another helper in a clear column, and define a name for that spill. Apply that name to the new item cell. Converting the input area to a Table does not create this row-specific helper setup for you.

When the category changes, clear the old item

Here's the catch: changing the available choices does not automatically clear a value already stored in the item cell.

Choose Fruit in B3 and Apple in C3, then change B3 to Office. The item dropdown should now offer Notebook and Pen, but Apple can remain in C3. The helper has recalculated; the stored selection hasn't been replaced.

Office selected as the category while the item cell still contains Apple and its dropdown offers Notebook and Pen — With Office in B3, C3 still contains Apple while its open list offers Notebook and Pen.

After changing a category, clear its item cell and select an item from the new list. Do the same if you delete or rename a selected item in the source Table. This setup updates the choices; it doesn't include a macro or other mechanism to erase old selections.

The legacy INDEX/MATCH method for older Excel

The original tutorial used Tables and defined names in Excel 2007/2010 for Windows and Excel 2011 for Mac. This section preserves that approach for workbooks without FILTER, UNIQUE, and SORT. Those historical versions are the context of the original instructions, rather than a claim that they have been retested for this refresh.

Use this as an alternative source layout, not an extra step in the modern setup. Create a Table named Table1 with the category names across the header row and their items underneath:

Fruit	Drinks	Office
Apple	Tea	Notebook
Pear	Coffee	Pen
Plum

Keep each list at the top of its column, with at least one item per category. Empty cells may follow the last item, but there must be no gaps within a list. Unlike the modern example, this method keeps the source order and does not remove duplicates.

Define the category and item references

Create the workbook-scoped name myCategory referring to:

=Table1[#Headers]

Formula

For a single Category/Item pair in Entry!B3:C3, set B3's List validation Source to =myCategory. Choose a category before setting up the dependent list.

Create another workbook-scoped name, myItemList, referring to:

=INDEX(Table1,0,MATCH(Entry!$B$3,Table1[#Headers],0))

Formula

MATCH finds B3's category in the headers using an exact match. INDEX then returns the corresponding Table column; the zero row argument requests the whole data column.

That reference includes every data row in the Table. For Fruit, all three cells contain items. For Drinks and Office, the third cell is blank. Using =myItemList directly as the validation source therefore leaves blanks in the shorter dropdowns. As the original example showed, that isn't an elegant list.

Create myItem, the final workbook-scoped name:

=OFFSET(myItemList,0,0,COUNTA(myItemList),1)

Formula

COUNTA counts the occupied cells in the selected column. OFFSET uses that count as the height of a one-column reference, starting at the top of the item list. Set Entry!C3 to List validation with Source =myItem.

These references deliberately fix the parent at B3. For another input row, create corresponding item names that refer to that row's parent and apply the matching validation source. Copying this fixed reference to another row would still make its choices depend on B3.

The blank-cell limitation matters

OFFSET is volatile, so extensive use can add recalculation work. The more immediate problem here is that COUNTA gives a count, not the position of the last item.

Suppose Fruit contains Apple, a blank cell, and Plum. COUNTA returns 2, so the formula takes the first two cells: Apple and the blank. Plum is left out. Formulas returning "" are another complication because COUNTA counts those cells even though they look empty.

Keep these legacy lists contiguous and use genuinely empty cells below them. If gaps cannot be removed, =myItemList includes the full column and avoids truncating later items, but the dropdown will include the gaps. It doesn't solve the blank-entry problem.

The non-OFFSET list tutorial shows how INDEX can return a whole Table column for a growing list; that alternative also includes blanks in the referenced column. If your existing source stores each category's items across a row, see the separate horizontal-reference example, which uses a different source arrangement.

Enjoyed this guide?

Join our newsletter to get the latest Excel tips delivered to your inbox.

You can unsubscribe anytime. See our Privacy Policy.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

RajanNovember 26, 2011 at 04:26 PM

Thanks it is helpfull

jokulaJanuary 11, 2012 at 09:37 AM

hi gregory..i was trying this excercise out and am getting an error message when i put in this Table1[#Headers] . I tried googling but cant figure out any '#' tag functions. Am lost?

Gregoryexcelsemipro.comJanuary 12, 2012 at 03:14 AM

Make sure you have inserted a table into your Excel spreadsheet. Then make sure your table has the name Table1. (You can do this by going to an empty cell and hitting the equals key (=), then selecting the table header. The resulting formula should be =Table1[#Headers] which you can see in the formula bar. (If you've added more than one table, your table might be named Table2). The cell will show #VALUE! because this is and array formula that returns more than one value. And don't forget to enter the equals sign when you enter this formula as a defined name (in the Edit Name dialog box).

jokulaJanuary 12, 2012 at 05:19 AM

Thanks greogry!!..it worked. I had just named it as Table1 and not 'inserted' a table as u mentioned. Very informative, Thanks again!

Gregoryexcelsemipro.comJanuary 12, 2012 at 05:22 AM

Glad I could help.

LorettaJanuary 16, 2012 at 04:11 PM

How can I do this with a shared files? Excel Tables are not supported in a Shared Excel File.

Gregoryexcelsemipro.comJanuary 17, 2012 at 01:30 AM

@Loretta, you have to replace references to the Table with a range reference. For instance, the top row of the Table, the Header row, can be named myCategory, if defined names are allowed in shared files. Otherwise you can use the range reference D1:F1 where you see myCategory. Any references to Table1 can be replaced by its range reference, which is D2:F( in this blog article example. The defined name for myItemList would become =INDEX($D$9:$F$9,0,MATCH(Sheet1!A2,$D$1:$F$1,0)) but the problem with this is that the table is not dynamic. If you add data to the table (that's not a table) you have to change the range reference. The alternative is to add defined names for the table, in other words create a defined name, like myTable, and give it the range reference of your table range, minus the header. If you want to add data to the table, insert a row between one of existing rows of the table, not at the end, and the table range will automatically resize.

Lalit bhatiaJanuary 19, 2012 at 10:18 PM

Hi Gregory, this is an amazing tutorial. Very helpful indeed. I have one question. In the first dropdown if I change the value, how do i clear out the dependent column - So if I update A2, i want B2 selection to be cleared thanks Lalit

Gregoryexcelsemipro.comJanuary 20, 2012 at 06:00 AM

@Lalit you can do this with some VBA code in the worksheet. Use the keyboard shortcut ALT+F11 to bring up the VBA Editor. Make sure the Project Explorer is showing (select View > Project Explorer if not showing). Then double-click the sheet with the drop-down list, like Sheet 1. Then at the top left box where is says (General) select the drop-down handle and select Worksheet. Then select the drop-down handle for the top-right box and select Change. Then type in the following code (starting with If Target):


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A2").Address Then
        Range("B2").ClearContents
    End If
End Sub

Now every time the contents of cell A2 changes, cell B2 will have its contents cleared.

Lalit bhatiaJanuary 20, 2012 at 03:17 PM

thanks Gregory, Appreciate your quick response. This kinda works. Only issue is that B2 gets cleared on select rather than on change. so if i go back to A2, B2 clears even if i haven't changed the value

Gregoryexcelsemipro.comJanuary 20, 2012 at 03:26 PM

You picked Selection_Change instead of Change. I did the same thing at first. Just go to the top right drop-down box and make sure you pick Change, then move the code and delete Selection_Change.

Lalit bhatiaJanuary 20, 2012 at 04:25 PM

thanks.that worked. Just fyi, to apply this for an entire column, (i.e if i change any item in column B, the corresponding value in C clears), the code looks like If Target.Column = 2 Then Target.Offset(0, 1).ClearContents End If thanks again

HectorFebruary 6, 2012 at 11:33 PM

You are a genius. This is the best system for dependent validation lists I have found after countless hours of search. Wish you a lot of success.

Gregoryexcelsemipro.comFebruary 11, 2012 at 08:40 PM

I appreciate the comment.

HectorFebruary 6, 2012 at 11:46 PM

Hello, I have following question. Lets say you selected a value in column A, as Fruit, and then in column B you select Banana. Now if you go back to the same row, and you change the selection in column A to Vegetable, in column B you still have Banana. Is there a way around to block the list in column A when there is a value in Column B? Best regards and thank you for your great solutions!

Gregoryexcelsemipro.comFebruary 11, 2012 at 08:51 PM

Yes, but it requires some VBA code. @Lalit had the same question, which I answered and you should be able to review my answer and her response in the comments above.

HectorFebruary 17, 2012 at 11:11 AM

Dear Gregory, thank you very much for you help!

LisaFebruary 22, 2012 at 02:55 AM

Dear Gregory: I have followed the instructions several times but encounter an error when I attempt to set the data validation for the Item B2 cell, I receive the following, "The Source currently evaluates to an error. Do you want to continue?\" Any ideas as to what I am doing incorrectly?

Gregoryexcelsemipro.comFebruary 22, 2012 at 06:22 AM

I haven't seen that error. Let me find the file I used in the blog post and set it up to be downloaded. This will give you a live example that you can use to compare it to your file. (It might take me a day or so to get this done) If that doesn't work you may send me your file so I could take a look and see where the problem resides.

Gregoryexcelsemipro.comFebruary 23, 2012 at 04:18 AM

I posted a link to the file, but here's another link just for you. Let me know if you are not able to fix the "error" you are getting and we'll figure out what's wrong.

LisaFebruary 24, 2012 at 04:03 AM

Hi Gregory: Thanks again for the quick reply. I still cannot get this to work correctly. Still getting the same message. I am sure my mistake should be obvious but I cannot see it. If I try and insert data valadation in cell B2, I get the same error message - “The Source currently evaluates to an error. Do you want to continue?” However, it worked when I tried it in cell B1, which contains the Header label "Item." Any ideas?

GeezerFebruary 28, 2012 at 09:41 PM

Same for me. I have tried this numerous times and it looks a great idea but I always get stuck with this same issue and error message - it met be something simple… but inscrutable

Gregoryexcelsemipro.comFebruary 29, 2012 at 02:18 AM

The problem that @lisa had was indeed simple. The myItemList formula =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0)) was slightly off. She had typed D2 instead of A2 for the cell reference and that evaluated to an error.

LailaMarch 9, 2012 at 10:21 PM

Geezer, were you able to find a solution. I'm getting the same error and can't find my mistake. Tx,

DaveAugust 1, 2012 at 03:38 AM

If you don't have anything in the first column selected, your second list won't work and will get that error - just select some fruit or something in A2 before you go ahead and set up the data validation in B2.

Gregoryexcelsemipro.comAugust 3, 2012 at 12:57 AM

Yes, when there is no selection in the first column, the second column drop-down does not have a reference for the formula. That's how it's designed. There are ways around this by writing an IF/THEN statement, which directs the Data Validation to do one thing with a blank cell and another if not.

MarkFebruary 22, 2012 at 02:05 PM

Great walkthrough - I have one question - Is there any way to create a third tier? For example - Category Item Make Fruit Carrotts Asda Carrotts Sainsburys Carrotts etc etc

Gregoryexcelsemipro.comFebruary 23, 2012 at 04:08 AM

A third tier may be possible, but it makes my head hurt to even think about it. Using the second level column data as the third level column header may be a starting point, but if you have multiple columns on the second level this would mean several third level tables, which means that a single lookup might be difficult, or unworkable unless you use a consolidated range. Now I'm headed for the Tylenol.

Steph P.June 18, 2012 at 09:05 PM

Wonderful article! I just applied your process to a third tier. My columns are "Category", "Type", and "Subtype". Table1 has Category for headers and Type for values, and an additional Table2 has Type for headers and Subtype for values. I found I could skip the step of naming the Table2 headers (it's not needed for data validation of the 2nd tier: that is done with the first table and your original steps.) I repeated the steps to Create a First Defined Name (this time for "mySubtypeList") and Create a Second Defined Name (this time for "mySubtype"). That final name was used for data validation of the third tier.

Gregoryexcelsemipro.comJune 20, 2012 at 04:15 AM

That sounds totally awesome! Now if I could just figure it out just exactly what you mean ...

RenataJuly 11, 2012 at 02:29 PM

I'm trying to follow this, but can't make it work... it gives me the usual error “The Source currently evaluates to an error. Do you want to continue?\” when doing Data Validation. I tried with all the cells to refer to I can think of bu no change... Anyone can help?

Gregoryexcelsemipro.comJuly 12, 2012 at 02:25 AM

@Renata, go the the bottom of the article where you'll see a link to "download this file." Click the link, download the file and see if you can follow what I did. Maybe you can see where your error is located.

RenataJuly 12, 2012 at 06:11 AM

@Gregory, thanks. But actually I managed to create the system (and working perfectly) for a second tier, now I need the 3rd thier, like it was explained by the other guy (Steph P). This is the bit that I cannot make it work :( And I need to deliver the file in few hours, argh, "grattacapo" as we would say in italian. Thanks anyway, and great job, great system, really very useful, thanks Gregory!!

SarahFebruary 23, 2012 at 05:17 PM

Hi Gregory. Thanks for the great tutorial! I have a question-- how would I set up a dynamic dependent drop-down list if I wanted a user to be able to enter any date in the second field column. So, First column has a drop-down menu created from a defined name, and then, based on what value in the defined name is chosen, an answer or a blank cell will appear which can be modified with any date. I get the theory, but not how to put it into practice.

Gregoryexcelsemipro.comFebruary 24, 2012 at 01:02 AM

You can't have it both ways. If you choose a List in the Data Validation dialog box you can get anything from that list, but only that list. If you pick Any Value, then you can enter anything, even a date, but you don't get anything from a list.

DanMarch 16, 2012 at 07:54 AM

Hello Gregory, thank you for the tutorial. I think I followed closely the instructions but I get the following behaviour: regardless of the category I choose, the list in the items drobdown is the firs column of the Table1 (including the header of column 1). I cannot find the mistake. Thank you

DanMarch 16, 2012 at 08:08 AM

got it - I was mixing some names and, consequently the referenced cells.

AlvaroMarch 20, 2012 at 12:06 AM

Hello; I notice that when adding a new row to the table you crated, you are still allowed to select the category but not the item, the list for the item is no longer there. I'm having the same proble with my file, can you help! Thanks

Gregoryexcelsemipro.comMarch 20, 2012 at 03:01 AM

I've had a few people tell me they've had some trouble getting all the steps right, so I added an [UPDATE] section to the bottom of the post with a link to the file I used. Please download the file and you can follow what I did, and perhaps see where your work differs. If you still have issues, please use the Contact page and send me an email. We can figure it out together.

AlvaroMarch 20, 2012 at 05:13 PM

Hello Gregory@ Tried again with the updated link, and recreated my excel file, i'm still getting the same problem... Please help!

TrishMarch 22, 2012 at 06:35 PM

I'm glad I found this. I have been using the dependent drop down validation list, but putting the OFFSET formula into the data validation field, rather than using the table mehtod described above. I was going to convert what I had done using the instructions above. Does it matter if I have created the tables already? My table is"Equipment". The column that defines the dependency in Equipment is "Category" and the dependant column is "Model_Name". Here is what I currently have in the data validation field: =IF(E2="",Category_List,INDEX(Category_Column,MATCH(E2,Equipment_Column,0))) I am defining the "Category_List" as a named range using the offset commands: =OFFSET(Variables!$A$2,0,0,COUNTA(Variables!$A:$A)-1,1) I am defining the "Category_Column" as a named range using the offset commands: ='Equipment Models'!$A:$A I am defining the "Equipment_Column" as a named range using the offset command: ='Equipment Models'!$I:$I Would like to simplify this if at all possible and use the power of the table functionality. Suggestions?

Gregoryexcelsemipro.comMarch 24, 2012 at 07:33 PM

@Trish, based on what you said and what I understand: Your first data validation list would be the defined name myCategory and the formula is =INDEX(Equipment[#Headers]). The second data validation list will be another defined name myItemList and the formula is =INDEX(Equipment,0,MATCH(E2,myCategory,0)). Notice that in this last defined name formulas that I used your reference to E2, and I'm not sure which sheet it's on. The second thing is that I used myCategory for the MATCH reference, instead of retyping INDEX(Equipment[#Headers]).

Paulexcelsemipro.com/2011/05/a-dyn…March 28, 2012 at 01:19 PM

Gregory, I am trying to use the formula....=INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0))....in a spreadsheet where I want each row to return a selection made from my table. I have created the table in a seperate sheet and when I use the formula I only get the selection I made in the first row. My formula is.for a defect type with the name Defect_2 in the name manager..=INDEX(Closed_Lists!$G$2:$L$9,0,MATCH(Tracker_List!$J$11,Closed_Lists!$G$1:$L$1,0)).. The idea being that I select a defect from the table in cell J11 and cell K11 gives me a second list of defect types from the table to choose from. I copy down the formula through the spreadsheet, but can only get the subsequant rows to return based on what is in row 11. Any help please?

Gregoryexcelsemipro.comMarch 29, 2012 at 02:57 AM

Your formula should work, but it's an array formula and hence you only see the first cell. Put your formula in a Defined Name, and call it something like myList. Then create a Data Validation list in a cell, and the list is =myList. Then you'll see the full list from the table.

Paulexcelsemipro.com/2011/05/a-dyn…March 30, 2012 at 02:46 PM

Gregory, Does the data validation have to be in every cell in a particular column or just the first one? I'm not all that good at excel and didn't fully understand how to do as you suggested. Could you provide a little more guidance please?

Gregoryexcelsemipro.comMarch 31, 2012 at 12:48 AM

I'm using Data Validation in this example to simply show a list of value in one cell. I wrote an article on Data Validation you can read by clicking this link.

DanielApril 6, 2012 at 02:03 PM

Gregory, Great tutorial, very helpful. Is there a way though to build the same dynamic dependent lists if the table headers were actually rows and not columns? For example, if the table was set up like this: category values ---------------------------------- fruit Grapes fruit Apples vegetables Celery other stuff Bread other stuff Yogurt I would still like to have a category drop down, and then based on the category chosen, the values dropdown would only show those associated to the specified category.

Gregoryexcelsemipro.comApril 8, 2012 at 01:12 AM

Yes, but your table needs to look something like this: Fruit Grapes Apples Vegetables Celery Beans Other Stuff Bread Yogurt Name the first column myCategoryH in a named range, then define another range name for the rest of the table and call it myTableH. Next create a defined name myItemListH and the formula will be =INDEX(myTableH,MATCH(Sheet1!D1,myCategoryH,0),0) The second argument of the INDEX function will get the row match, and putting a zero in the last argument will return the entire row. In cell A2 set the data validation for a list =myCategoryH, and in cell B2 set the data validation to a list =myItemListH. Now you have a horizontal lookup.

monica HaganMay 16, 2012 at 05:04 PM

Gregory, I really appreciate you sharing the configurations for the dynamic depend list. I have a question I hope you can help me. I am trying to create a very simple database using the form function from excel. Actually I downloaded the J-walk addin so I can use depended lists for several of the entries. My problems is that when I try to use the dynamic entries for the selection in the form, the list does not come up. But when I try to select the entries on spreadsheet it works fine. Let me know if you can help me? Thanks Monica

Gregoryexcelsemipro.comMay 19, 2012 at 05:28 PM

I would need to see the spreadsheet and the form to answer your question. I haven't tried to do any dependent drop-down lists inside a form so I'm not sure how that would work.

Monica HaganJune 8, 2012 at 11:14 AM

Greg, I created a VBA code using combo boxes to accomplish what I was doing before with the form function in excel. I am able to assign named formulas to the combo boxes, but when I tried to assigned the depend box it does not seem to work.. Have you done dependent drop down list using combo lists in VBA? Thanks, Moniac

Gregoryexcelsemipro.comJune 10, 2012 at 07:41 PM

I have not tried to do this with VBA, but given the amount of interest in this particular topic I might have to do something that will automatically update with VBA. Sorry I'm no help. If you feel comfortable sending my your file, contact me and I'll take a look.

Monica HaganJune 20, 2012 at 07:20 AM

Gregory, Thanks. How do I send you a copy of my excell file? I love ot have some help.. Thanks,

VernonMay 18, 2012 at 12:29 AM

Hi Greg, I have a question. I am trying to create a drop down list where the options in the list show detail but when you select it, the display cell has a concise version of the selection. Is there a way to do this?

Gregoryexcelsemipro.comMay 19, 2012 at 05:36 PM

I'm not entirely sure I understand the question. What do you mean by "the display cell has a concise version of the selection?"

VernonMay 20, 2012 at 12:10 AM

Hi Greg, Here is an example of what I mean. In the cell where the drop down list exists. I want the drop down list to have a detailed explanation: eg 'orange: a citrus fruit' however once I select that option I want the cell to only show 'orange' Thanks again.

Gregoryexcelsemipro.comMay 20, 2012 at 04:40 PM

I don't know how to do that in the same cell. Debra Dalgleish has something about doing this very thing on her site, but VBA is involved. I don't think it's complicated.

LarryMay 23, 2012 at 09:09 PM

Hi Greg, I would like to build a spreadsheet that contains a department field. Then Based off of the department field a chart of accounts list will be filtred to the correct accounts for that department and allow multiple entries. Can you help me with this? Policy Etcc...

Gregoryexcelsemipro.comMay 27, 2012 at 05:20 AM

You could use this article and pretend that Category was the Department, and Accounts was the Item. Multiple entries would have to be in different rows. So instead of Fruit, Vegetables, etc. you would have Dept 1, Dept 2, etc. and then under Dept 1 you would list it's chart of accounts. If you're not sure about this send me an email on the contact page.

KamalaMay 25, 2012 at 08:47 AM

I tried creating a data table ,cross checked it points to correct name as Table2. Also created user defined name in similar to mycategory as DynT2 . Post this , in another sheet where i want to get this dynamic data tables value , i tried to include this DynT2 into the data validation of the cell .Post this Iam getting only the header values of T2 and nothing more . I don't understand why at all we had to create ItemList & Items User defined names and how it is linked to the cells.. I also tried this similar procedure in the same sheet still getting same issue. can you throw some more light onto this..

Gregoryexcelsemipro.comMay 27, 2012 at 05:28 AM

If you follow the article exactly it will work. Common problems are missing something simple that you don't see when you go back and look over what's been done. That happens to me more times than I like to admit. Try downloading the example file from the article. That will give you a working spreadsheet that you can tinker with and modify.

SueMay 25, 2012 at 10:43 AM

Hi Greg, I'm trying to create a conditional drop-down list that contains repetative information. I have 3 columns, one is Office (select the State the office is located), next column is Department (eg Banking, Property) and the third is a list of staff members within that particular office working in the selected department. The departments are repeated for each State an office is located. I've been able to set up the first two columns correctly but because the departments are repeated for each State I'm not sure how to populate the appropriate staff members in a drop-down list. If I select the department as "Banking" in one row then the next time I select "Banking" I get the same staff members listed even though the offices are in different States. Just wondering if you can help me this? Any help would be greatly appreciated.

Gregoryexcelsemipro.comMay 27, 2012 at 05:25 AM

It sounds like you have to concatenate the State and Department so that you have a unique list for staff members. AK-Banking, CO-Banking, HI-Property, MN-Property, etc.

Raymond TsanMay 31, 2012 at 06:13 PM

Hey Gregory, I know your defintely getting a lot of praise for your brilliance and you deserve it. It took me a while to read through all the interesting comments but I didn't see my question be asked so here it goes: Is it possible to choose from the drop down list and instead of filtering the results, display all the results. Here is an example. If you choose January, on A2, then ... January 25, 2012 January 26, 2012 January 27, 2012 January 28, 2012 January 29, 2012 January 30, 2012 January 31, 2012 pops up. If we chose February, February 23, 2012 February 24, 2012 February 25, 2012 February 26, 2012 February 27, 2012 February 28, 2012 February 29, 2012 shows in the same cells as January if I chose that prior. I didn't think it was going to be this hard to figure out! Is this even possible on Excel? Thanks a lot, Ray

Gregoryexcelsemipro.comJune 4, 2012 at 12:04 AM

I don't know how to show the days of the month in the same cell that you choose the month, i.e., cell A2. You can have the days show up as a list in cell B2, but I'm sure you've figured that out. Sorry I couldn't be of more help.

SusieJune 28, 2012 at 02:22 PM

I was able to follow your directions above and successfully create one dropdown based on another on a sample file with Sheet1 as sheet name and using Colunmns A2 and B2 in that sheet but when I tried to adjust so I could use on my Working file and Sheet called external 'IHD Hiring Sheet' and Columns F and G, I cant get the second drop down, in column G (based on data in Column F) to work. I am sure I am missing something simply, I hope you can help.

Gregoryexcelsemipro.comJune 29, 2012 at 12:54 AM

It's not clear to me what you did to "adjust" the sample file to the working file. It might be best to remove what you have, and start over in your working file. Or you could use the Contact page on this blog and email me a better description of how you made the adjustment, from sample file to working file. If the working file is something you could share, then we can work out how to get me the file to take a quick look. It's probably something simple like a range address that is slightly off.

Scott WhitneyJuly 28, 2012 at 05:37 PM

This is a wonderful resource. I'm using it to evaluate my student's sign language performance. Instead of having to retype the same comments again and again, I put the comments in a drop down. I also put the vocabulary they need in the story in a dropdown, then created columns of error1, error2, etc. with the same vocabulary list copied using a formula. With the vocabulary in a table, I can automatically update it adding new vocabulary. I just modified it a little to get the student's names in the first evaluation column, then added the errors in the subsequent columns. It's set for pivot tables now too. Thanks a ton!

Karthiga BalakrishnanJuly 30, 2012 at 11:33 AM

Hi Greg, I was looking for a way to provide dropdown values based on a value chosen by the user, and your post has helped me with just what I needed. Thank you for this post, it has been very helpful. I've tried to implement this solution in one of the situations where there are too many categories and few items under each category! So maintaining these categories in a conventional table means several columns with few values under each column. It would be nice if I were to maintain the categories in a single column and list the values for each category in the same row against each of them -- simply put, I want to transpose the table values for easy maintenance and still need this solution to work somehow [am I expecting a magic to happen here :|] appreciate any help or assistance you can provide. Thank you.

Gregoryexcelsemipro.comAugust 8, 2012 at 01:41 AM

Sorry for the delay in responding. I wrote a post on this topic: A Dynamic Dependent Drop Down List with a Horizontal Table Reference. Essentially you can transpose your table values and the category headings will now be row headings in the first column. This means you can't use an Excel Table because, by default, the headings are in the top row. You can use a named range for the data table and then alter the formulas as I did in the aforementioned post. Updating the table is a little tricky as you have to be careful to make sure the defined name references the entire table. It won't automatically update. You have to add data to the table, then update the defined table name so the formulas will work. This can be done with a little VBA, but is probably outside the scope of your original question.

LaurenJuly 31, 2012 at 03:29 PM

Hi Gregory! Really helpful tutorial I just have one question, when you are creating the Defined Names for ITEMS are you selecting the whole table when defining the name or just the items without the headers? Thanks!

LaurenJuly 31, 2012 at 09:37 PM

Sorry! I figured it out, it was not like that at all.. And again thanks a lot for the tutorial, really helpful!

Gregoryexcelsemipro.comJuly 31, 2012 at 09:52 PM

Thanks for the compliment, and I'm glad you figured it out.

CameronAugust 2, 2012 at 02:46 PM

This has worked fantastically for me, thanks. I am having one problem though, where I'm trying to make the dependent list across 3 columns, the data validation says that "The Source currently evaluates to an error" and the drop down does not appear on any column but the first one. Any ideas?

Gregoryexcelsemipro.comAugust 3, 2012 at 12:49 AM

Possible reasons for the error: - There is an error in your formula. - When you set up the defined range formula for the third column, you didn't have the first cell selected, which throws off the relative reference. - Your formula is an absolute reference instead of a relative reference. ($C$2 instead of C2) Hope that helps.

ViktorAugust 8, 2012 at 08:00 AM

Hi Gregory Many thanks for an very useful guide - unfortunately I also get stuck with the same error message as with Cameron. I suspect that it has something do with the naming of the items (one previous comment mentioned that she "figured it out" - unfortunately she did not elaborate). It would be very helpful if you could clarify the following: 1. Do you select all items when naming them? 2. In your file - how come you refer myItemList to C2 (an empty cell)? 3. My "table" contains filers on top - not sure if this can be a problem? Many thanks, Viktor

Gregoryexcelsemipro.comAugust 14, 2012 at 11:57 PM

1. The only cell I selected when creating a defined name was myItemList. And that cell was B2. The reason is the formula in this defined name will reference the cell immediately to the left of the active cell. So you have to select the correct cell and then change the absolute reference to a relative reference, meaning the $A$2 is changed to A2. This allows the formula to be copied down and each reference will be to the cell immediately to the left. The tables create their names automatically. The other defined names, myCategory and myItem were just typed in because there was no cell or range reference. The only time I select all the cells is when I create a defined name for a range. 2. If you select cell B2 and then choose Insert > Name > Define and select myItemList, you will see a reference to A2. If you select cell D2 and do the same thing, you will see the cell reference is C2, which happens to be the cell to the immediate left. So myItemList will show the cell to the immediate left of the active cell. 3. The Table filters aren't a technical problem, everything will work fine with the filters on. However in this instance I don't care for them because they get in the way of reading the column headings and they aren't used. So I turn the filters off by choosing Data > Filter. Hope this helps.

J.H. LEONGAugust 8, 2012 at 06:04 AM

I am having one problem though, where I’m trying to make the dependent list in first column, the data validation says that “The Source currently evaluates to an error” and the drop down does not appear on any column. Gregory started with third column, but I happen when the first column.

J.H. LEONGAugust 8, 2012 at 06:43 AM

Gregory, I found my problem, thanks, very helpful.

ChipAugust 11, 2012 at 05:09 AM

Best tutorial on the subject! Thanks! However, I really struggled to make this "dynamic" to an entire column. Meaning: $A$1 limits the drop-down options for $B:$B. Your examples always include a single-cell reference for the MATCH value ("MATCH(Sheet1:A2") but I really wanted to have $A$1 limit the drop-down for $B$1 and $A$2 limit the drop-down for $B$2 and so on. I first tried to replace your single-cell reference with a named range reference ("CategoryCol"), but no such luck. Then I realized I needed to have the formula first reference the specific cell I wanted to match. I was able to adjust my formula to create dynamic drop-downs in the spreadsheet columns that are relationally connected to the "parent" column on a row-by-row basis. Example: =INDEX(Table1,0,MATCH(INDIRECT(ADDRESS(CELL("row"),COLUMN(CategoryCol))),Table1[#Headers],0)) However, with your mad skills, I'm wondering if there is a simpler, more stable solution than the one I found?

Gregoryexcelsemipro.comAugust 15, 2012 at 12:25 AM

The single cell reference is designed to be relative. Meaning that I select cell B2 and create the myItemList defined name and refer to cell A2. If $A$2 is the reference then the formula will only reference the first cell. By using a relative reference, A2, the myItemList always refers to the cell just to the left of the active cell. Therefore it can be copied down column B and it will work perfectly. The key is to create the relative cell reference in the first row while the active cell is B2, and the reference is cell A2. Once this is done your set.

ChipAugust 22, 2012 at 02:55 AM

Thanks, Gregory. That does make sense. I did re-try your sugtion to create a simpler formula. I couldn't apply this validation rule to the entire column successfully, which is a key for me since I can't predict the number of rows that will ultimately be used. However, I also cannot turn the dynamic drop down sheet into a table, which I think you said would maintain the validation. My formula, while clunky, does handle these restrictions. Thanks again. You let me impress my boss with my semi-pro skills!

Gregoryexcelsemipro.comAugust 22, 2012 at 03:50 AM

If you put your reference table on a different sheet and give it a defined name, you can have the range name automatically update with some easy VBA code. I wrote a simple post to cover this technique.

RaymondAugust 25, 2012 at 04:44 PM

Nice tutorial. I used to take advantage of pivottables to dynamically get the content of my named range since PT can give sorted lists without duplicates and without blanks (offset and countA the PT list as final). The workaround was to trigger my RowField everytime an item was choosen in, say listbox_1 to get the dynamic contents list ot that item in, say listbox_2. Always good to learn new methodes that can do the same job :-)

JamieAugust 30, 2012 at 12:06 PM

I'm having a little weird behavior with this. I have adapted this to a workbook I have, with the table headers Expenses and the items underneath are SubCategories for the Expenses. I have used ExpenseList as the defined name with the INDEX formula, and ExnenseListSub as the defined name with the OFFSET formula. My primary validation is in column D, starting on cell D68. I've noticed that when I choose something in cell D68 from the validation list, the ExpenseList formula changes to something funny, like =INDEX(Table1,0,MATCH(AUG!$D104954,Table1[#Headers],0)). AUG!$D104954 is not in the range I want to use for the table, which should only go from row 68 (67 with headers) to around row 225, will probably never need more than that. Any clue why this is changing? Thinking about it, I looked at the example provided in this post, and saw the same thing happening. In the defined name myItemList, A2 is changing to some whacky cell, such as XFD2, so the formula reads =INDEX(Table1,0,MATCH(Sheet1!XFD2,Table1[#Headers],0)).

JamieAugust 30, 2012 at 12:22 PM

I just noticed, the INDEX formula AUG!$D#### is changing to whichever cell I have currently selected in column D, as long as only the column is a fixed value. I made it relative, and it changes to wherever I have the currently selected cell, and sometimes changes to the last possible row (100454095 or something like that) or the last column possible.

Gregoryexcelsemipro.comAugust 31, 2012 at 01:22 AM

Hopefully my previous response to your earlier comment will explain everything.

Gregoryexcelsemipro.comAugust 31, 2012 at 01:20 AM

The main problem is when you create the myItemList defined name formula, which is =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0)) in my example, the Active cell must be where the first formula will appear. In my example, when I created this formula the active cell was B2. After this is done, your formula will work correctly. The reference in this defined name formula is cell A2, but that is misleading because the reference is relative, meaning that it will always refer to the cell just to the left of the active cell. If the active cell is D30, and you take a peek at the defined name formula, you will see a reference to cell D29. Similarly when the active cell is in the first column, say cell A68, then the reference will be to cell XFD68, which is the last cell in the same row. The reference wraps back around, so to speak. Now if you create this defined name formula, for instance when the active cell is cell C20, and you use the same A2 cell reference, then the defined name will always refer to the cell that is two columns to the left and eighteen rows above it. If you then use that defined name formula in cell B2, then you'll get a reference that is near the bottom, right area of the entire worksheet, because the reference will "wrap back around." So you should use an active cell in row 68, where ever you are going to use the formula, then open the myItemList defined name formula and change the cell reference to the proper column where your data is and then the cell reference will work all the way down to the end of the last row. And example would be that the data is in cell D68 and you want the formula in cell E68. Then select cell E68, open the defined name formula myItemList and change the cell reference to cell D68. Then you're good to go. Hopefully that helps.

tamerSeptember 5, 2012 at 02:47 PM

Hi Gregory, i am using excel 2010 and trying to figure out if the following scenario is possible; any feedback is appreciated.. if i have 5 line items for example that their data is dependent on me choosing a reference (number or letter) from a drop down menu; when choosing the reference (number or letter), is there any way that the cells take the value of the reference from a range that i pre set? ill try to explain below: A1 = blank A5 = blank A9 = blank A22 = blank A71 = blank my control drop down menu is the following: AA, A, B & C. The data range for those is AA=50, A=100, B=250 and C=500 if i click on B from the drop down menu, (think of it as a legend) i would like for A1, A5, A9, A22 and A71 to all have the value 250. thanks!

Gregoryexcelsemipro.comSeptember 7, 2012 at 04:51 AM

Yes there is a solution. Here's how I did it. In cell C2 add a Data Validation List by typing in AA, A, B, C. Create a defined name of that cell called myDropDown. Next enter a lookup table with the same list and their corresponding values. Cell E2 = AA, cell F2 = 50, cell E3 = A, cell F3 = 100, cell E4 = B, cell F4 = 250, cell E5 = C, and cell F5 = 500. Create a defined name for the range E2:F5 with the name myLookup. Next enter the following formula in cell A1 =IF(myDropDown="","",VLOOKUP(myDropDown,myLookup,2,FALSE)) and then copy this formula to cells A5, A9, A22, and A71. Now when you select a value from cell C2 from the drop-down data validation list, the formula looks at the list value (AA, A, B, or C), looks up the corresponding value, and returns the result to all those cells.

tamerSeptember 9, 2012 at 07:25 AM

Hi Gregory, Just wanted to say thank you; worked perfectly!! Thanks for taking the time and even sending me an excel sheet - much appreciated.

FLAYYMzSeptember 11, 2012 at 01:43 PM

Hi Gregory, Thank you for your article - I've successfully applied it. However, I need to create a third column, which is dependent on the second column (itself dependent on the first column). The trick is, for column 3 I want to reference a new table, of whose name matches the value in column 2. I.e. I want to replace "Table1" in the below formula with the value of the adjacent cell in the previous column, in an effort to reference the appropriate table. =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0)) Does this make sense? Thank you in advance!

Gregoryexcelsemipro.comSeptember 17, 2012 at 12:19 AM

If you want a third column then create a Table2 by using all the data values in Table1 as the Header row. Then fill in the data in Table2. Next, while the active cell is C2 create a defined name formula, call it myItem2 and the formula is =INDEX(Table2,0,MATCH(Sheet1!B2,Table2[#Headers],0)). Now create a Data Validation list in cell C2 that is =myItem2. Hope that helps.

FLAYYMzSeptember 17, 2012 at 10:14 AM

Hi Gregory, Rather than place all data values of table1 into the headers of table2, I want to break it down with a separate table for each header in table1. Thanks

Elliotwww.paychecklawyers.comSeptember 17, 2012 at 06:58 PM

Hello, I'm trying to avoid using the =OFFSET formula because of its volatility. I have a worksheet that uses the original Contextures solution, but when I change data in a Date cell elsewhere on the same spreadsheet, Excel crashes. I know it's the Index-dependent lists that are causing Excel to crash because when I disable the "Counter" named range, my spreadsheet no longer crashes when I change the date cell. Has anyone else encountered a crashing problem with the Contextures method? Does anyone have a solution to the problem? Thanks, Elliot

HunterSeptember 27, 2012 at 03:19 PM

Hi, Have been tasked to create a database of suppliers globally, by my company. Each company contains 4x fields and the premise of the document is whether the company provides a service in that city. The geographical hierarchy is as follows Region, Country, City. What I want is to be able to have separate drop down filters for EMEA, Americas, AsiaPac and a static list of suppliers but be able to select different countries/cities and see who from the entire list provides services there. Really appreciate some advice how to set that up, 1x line example of basic information is listed below. Algeria Angola Alger Aberdeen AECOM www.aecom.com Key contact Email Address Y Y Thanks, Hunter

Gregoryexcelsemipro.comOctober 1, 2012 at 05:53 PM

These complicated examples are a little hard to visualize when not viewed in a spreadsheet and the description very precise and detailed. Perhaps you could send me an email from my Contact page. I'll respond and you can send me an example worksheet. I'm not getting the full picture here so it's hard to point you anywhere, much less in the right direction.

HunterSeptember 27, 2012 at 03:23 PM

Oops, example didn't come out very clearly, example below for EMEA Americas Algeria America Alger New York AECOM www.aecom.com Key contact Email Address Y Y

jose luisSeptember 28, 2012 at 12:30 PM

hi... i have a table of contracts, with the columns contract number, name, state.... in the same file, a different sheet was created in order to apply payments to the contract, i used a drop down list in the cell where i can select the contract number, based on the contracts table previously created; i have to insert rows for every payment made... sometimes there are different contracts for the same providor, so i want to be able to display only the contracts that the selected providor have, as per the contracts table...is there a way to do this using just one table (contracts)?

Gregoryexcelsemipro.comOctober 1, 2012 at 06:02 PM

Your example is a bit confusing. You want to reference a provider, which may have more than one contract, but you don't list provider in the table of contracts. And when you say you are "inserting rows for every payment" are you really inserting rows or adding different records for the same provider? I don't really understand the question here. If I would take a guess, on displaying only the contracts for each provider, that would take an Array formula and a (variable) number of cells to return that information. Not really my speciality.

Jose LuisOctober 2, 2012 at 05:16 AM

let me try to explain it... sheet 1: (a1)contract number (b1)provider (c1)contract amount (a2)101 (b2)company 1 (c2)$10,000 (a3)102 (b3)company 2 (c3)$5,000 (a4)103 (b4)company 3 (c4)$4,000 (a5)104 (b5)company 1 (c5)$3,000 sheet 2: (a1)provider (b1)contract number (c1)payment (a2)*validation data list from the contract names of the sheet 1 (no problem doing this list)* (b2)*here is what i need help for, I need to create another list which displays only the different contract numbers that the providor chosen in the cell a2, has in the whole base of the sheet 1, so do not need to write the whole contract number that can cause a misswriting data and, therefore, can´t get the report of how much have been paid to that particular providor (I am talking about 600+ contracts with around 350+ different providors); in other words, if I choose in cell (a2) the option "company 1", in the list of the cell (b2)should only show the contract numbers "101" and "104" only... so I can register in the cell (c2) the payment made to that providor for that specific contract (it could be lots of payments for each contract)...therefore, the list of payments of this sheet 2 will be long based on the different payments made to the whole list of contracts... is there a formula or a way to do this avoiding any script, will be great... thanks for any advice

Gregoryexcelsemipro.comNovember 10, 2012 at 08:21 PM

This one is beyond me, formula wise. I would resort to some type of programming to see if that would work, but I'm having trouble seeing the solution.

JillOctober 2, 2012 at 05:45 PM

I've read through the comments, but something still isn't working for me...I actually copied and pasted your formulas as stated herein, so I know there are no formula issues. I get my first dropdown in just fine, but the second one won't work at all. I'm using the defined name "Customer2" for the second dropdown when I do the data validation. I have the following reference for "Customer2": =INDEX(Table1,0,MATCH(Sheet1!A3,Table1[#Headers],0)) Where Sheet 1, cell A3 is the location of the first drop-down entry upon which the second is dependent (I have my Table1 in Sheet 2). I have no idea why my second drop-down isn't working...any thoughts would be much appreciated!

Gregoryexcelsemipro.comOctober 2, 2012 at 05:59 PM

When you create the define name formula for Customer2, the active cell must be where the formula will return the first answer. For example, cell B3 might be where you would want the second drop-down list so that is the active cell where you would create the Customer2 defined name formula. The formula is actually a relative reference, meaning that, in this example, it will always look one cell to the left for the input.

JillOctober 2, 2012 at 06:24 PM

I thought I'd already tried that, but I just did it again, and voila! One of those things where I think I looked at it/tried too long...I'm an idiot - thanks for the quick response!

JooOctober 10, 2012 at 04:45 AM

Thanks a lot. It's very helpful even I've just used from your sharing a bit.

JenniferNovember 19, 2012 at 12:07 AM

Hi This has been a really helpful tutorial for me, i have applied it to some inventory data that i want to use to populate a schedule of quantities from. I have three levels of selection criteria (Category, Type, Size) on approx 1600 different items. My data is ordered by category then type then size. Category Type Size widget WA 100 widget WB 150 sprocket SA 50 sprocket SA 60 sprocket SB 50 Sprocket SC 60 To make this work i created two look-up tables on separate worksheets to generate the dynamic drop-down lists: One sheet with part categories as header with the unique part types listed beneath: Widget Sprocket WA SA WB SB SC A second one with part types as headers with unique sizes listed beneath WA WB SA SB SC 100 150 50 60 50 60 I used Index formulas to populate the look-up tables from. However with 20 categories and over 200 part types the sheet crashes every time i recalculate - frustrating! Is there a way to create a dynamic drop down list directly from a database listed as above? Thanks Jennifer

Gregoryexcelsemipro.comNovember 21, 2012 at 12:34 AM

So far as I know, Data Validation won't accept the complicated INDEX formula directly, hence the use of the Defined Name formula. Have you tried leaving your worksheet on auto calculate? Or does that slow it down too much? Sorry I'm not much help.

JenniferNovember 28, 2012 at 08:47 PM

Thanks, I seem to have got it working, by using index and offset formulas in the defined names to reference a unique name lookup sheet, then return data from the original database. Jennifer

Gregoryexcelsemipro.comNovember 29, 2012 at 03:22 AM

Fantastic! I'm glad you were able to figure it out.

NoelNovember 21, 2012 at 01:59 PM

Is there a simple way to save the selections made on sheet 2? My use case is that I have a table and I want to know how many times Johnny selects the combination of other stuff popcorn vs fruit grapes?

Gregoryexcelsemipro.comNovember 23, 2012 at 05:20 AM

Not that I'm aware of. Sorry!

DJGizboNovember 23, 2012 at 03:52 PM

Dear Gregory, I like this work alot! Now I want to make it even more dynamic and a new table. So first you choose in a dropdown the main course of the starter. Then from that choice the formula's get the info from Table1 of Table2. With indirect I made the choice for selecting the table, but I got a #ref. It looks like =INDIRECT('Table'&A1) does not work (indirect and dynamic names bug??). Can you please advise?

Gregoryexcelsemipro.comNovember 23, 2012 at 08:01 PM

If you follow the post closely it should work correctly. You may be missing something simple, so my recommendation is to download the example file at the end of the article and see how I've done things, and then compare that to what you are doing.

TimNovember 24, 2012 at 10:40 PM

Thanks for great info. I expect what I am asking is not possible without VBA, but going to ask just in case. How about creating the dynamic lookup from a single table with the first key being a value. For example, a single table of expense codes like travel, training, registration fees, Bank Fees, Interest Income, etc and a second column for each entry indicating income or expense. I would like to be able to select income or expense from a simple drop down (easy) and then have a second drop down populate with only the appropriate choices (harder). The key is the feed data is provided in a single table, and is updated from time to time. I went through the hassle of separating into two separate tables as necessary for this method, and even some VBA to do this automatically when requested, but it would be much easier and simpler to be able to pull the lists from the original table.

Gregoryexcelsemipro.comNovember 28, 2012 at 04:40 AM

The data for the data validation drop-down list does come from a single table. Not sure exactly what you are referring to there. For your example you would have a two-column table. The heading on the first column would be Income, with appropriate choices in the rows below. The second column of the table would have the heading of Expense, and have a similar list of choices for expenses below the heading. This table could be used to create data validation cell to select the Income or Expense, and then a second cell to dynamically make a selection. Hope this helps.

TimNovember 28, 2012 at 06:12 AM

Yes, I wondered if I was clear on my question. The data for the lists is provided in a slightly different form. The first column of the table contains ALL the categories. The second column contains the word inc, exp, or memo for each record, and then a third column with either active or inactive. This is not my design choice, this is the way the categories are provided to me. They are updated on a monthly basis. I have converted this to a table like suggested, with the first column containing only active expense tags, and the second only active income, and the third any memo tags. Works fantastic, but wanted to eliminate the monthly table conversion. I was hoping someone had an idea how to do this without converting the source table format, and without using vba.

KurtDecember 18, 2012 at 07:28 PM

I have successfully created dependent menu lists using the methodology on Contextures. It works great with one exception...it does not process well backwards. For example, in the first menu I select a Region (North America, South America, Europe, etc.). the second menu, which is dependent on the fiorst menu is country (USA, Canada, Mexico, France, Germany etc. and the third is city. Everything works perfectly except if I selected North America and then USA and then Boston and then decided to change the region to Europe the second and third menu selections stay as previously selected...which could lead to an incorrect reporting result. I would like for it to default to being blank when one menu is not possible given the selection of another menu. Any ideas? Thank you, Kurt.

Gregoryexcelsemipro.comDecember 28, 2012 at 08:47 PM

You could change the defined name formula to include an IF statement that looks to see if the reference cell is blank and returns a blank cell, otherwise does the lookup. Something like this: =IF(B5="","",VLOOKUP(B5,myVendors,2,FALSE)).

JunDecember 26, 2012 at 09:39 PM

Thanks for the tutorial, Gregory! One question. Is there a way to clear out the value on the Item (second column) when I select a different Category (first column)? In other words, if I originally selected Fruits as the Category and Bananas as the Item, and then I change the Category to Vegetables (on the same row), can Bananas be cleared out since Bananas are not Vegetables?

Gregoryexcelsemipro.comDecember 28, 2012 at 08:44 PM

You might change the myItemList defined name formula to: =IF(Sheet1!A2="","",INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0))) and see if that works. The formula returns a blank if the reference cell is blank, and otherwise does the lookup.

AbhijeetJanuary 16, 2013 at 07:36 AM

Hey Gregory, Thanks for the info..it helped me a lot.. Is there an option for adding the check box function also in the dependent list. For selecting multiple values from the drop down. Abhijeet

Gregoryexcelsemipro.comJanuary 17, 2013 at 06:05 AM

For a check box option you would have to go with VBA.

CoreyJanuary 28, 2013 at 03:25 PM

Gregory, Thank you for providing this tutorial. I have one problem though. I've created the dynamic list you provided in the example above, works great. However, my problem is that if I were to select "Fruit" as the category first, then selected "Banana" as the type, I can go back and then change my category from "Fruit" to "Vegetables", thus showing "Vegetables" as the Category and "Bananas" as the type. This is obviously not a valid combination. Is there a way to have the user select only valid combinations regardless of which box is selected first (i.e. if I select "Bananas" in the second box, the only choice I have for the first box is "Fruit")? Any help on this is greatly appreciated.

mikeJanuary 30, 2013 at 11:15 PM

I ran into the same error many others have, and one that you have addressed: "The Source currently evaluates to an error" - In general your instructions skip major parts of the building this model. i.e. What cells do you highlight when you create a "Data Validation" versus a "Defined Name"? - Thanks for all the free instruction though.

mikeJanuary 30, 2013 at 11:33 PM

ok i figured it out and feel better - excel can be frustrating...

arijitFebruary 18, 2013 at 08:54 PM

It doesnt work with excel 2007 =INDEX(Table1,0,MATCH(Sheet1!A2,Table1[#Headers],0)) =OFFSET(myItemList,0,0,COUNTA(myItemList),1)

Gregoryexcelsemipro.comFebruary 19, 2013 at 04:34 AM

Go to the bottom of the blog post where it says UPDATE. Then click on the link "download this file" and you will get a working file downloaded to your computer.

arijitFebruary 19, 2013 at 06:08 AM

Hi, I have download it but its only working on your file same coding is not working on mine...

ManuFebruary 27, 2013 at 11:16 AM

Hi Greg, Great post! It has helped me a lot. I have used it to create a dynamic drop down on a column of a table that reads the drop down list values from that same column on that same table. It is a great feature since it allows either selecting existing values or entering new ones. However, when I enter one value on the column more than once it also appears more than once in the drop down list. I have not been able to figure out a way of eliminating duplicates in the drop-down. But I am not a excel semi-pro as you ;) Do you have any hint to give me? Thanks a lot and keep the great work here! Cheers, Manu

Gregoryexcelsemipro.comMarch 3, 2013 at 08:25 PM

I'm sure there must be a way to do this, but since I'm a semi-pro and not a Pro, I couldn't tell you what the answer should be. Sorry I couldn't help. :-)

Jeff WeirApril 7, 2013 at 01:12 AM

Manu - can't you just use Excel's autocomplete feature to accomplish the same thing and ditch the data validation altogether?

MatanMarch 3, 2013 at 07:21 AM

Hi Gregory. Thank you for the lovely post and answers. I have a problem with the dependent lists, and I see it's also in the sample excel file you've distributed, and would really appreciate your assistance on this matter. The thing is that the secondary list doesn't update when I change items in the main list. For example, in your excel file, if I change A2 from fruits to vegetables, B2 still displays "Banannas". Only when I actually click on the drop-down menu in B2 do I see the vegetables. Is there any way to update the displayed value in B2 once I switch items in A2? Thanks. Matan.

Gregoryexcelsemipro.comMarch 3, 2013 at 08:21 PM

Assume for a moment that you have a Defined Name Formula that is used in Data Validation for cell B2, and the name is MyFormula and it equals SuperDuperFormula. If you want MyFormula to recognize when there is a blank cell in A2, then you should modify the formula as follows. First select cell B2, then go to the defined name formula MyFormula and change it to: =IF(A2="","",SuperDuperFormula) This modification to the formula will look to the first cell that is to the left of the active one, and if there is a blank it will return a blank, otherwise it will execute the SuperDuperFormula. For the most part, this approach works. However I believe that there is some event-driven behavior with the Data Validation that sometime interferes. Hope this helps.

Jeff WeirApril 7, 2013 at 04:22 AM

Gregory: I think Matan is talking about the scenario where a user has made a choice from the dropdown in B2, and then later goes and changes the dropdown in A2. In that case, the choice in B2 now no longer reflects the choice in A2. Matan: You would need a VBA event handler to monitor if a 'master' dropdown is changed, and then that code could clear the value in the 'slave' dropdown. Here's an example: https://sdrv.ms/Zphkl8

Gregoryexcelsemipro.comApril 7, 2013 at 08:09 PM

The link goes to the Microsoft Excel Web App, which does not support Data Validation or VBA Projecte Jeff: If your assumption regarding Matan's question is true, I typically add an IF statement to the defined name formula to look for a blank cell and return a blank, otherwise do the lookup.

Jeff Weirwww.heavydutydecisions.co.nz…April 7, 2013 at 09:34 PM

Forgot to mention that you have to download the file by clicking File then Save As. I'm not clear how the IF statement helps once a selection has been made in B2 and then the selection is changed in A2. I think Matan is talking about this scenario: 1. The user selects "Vegetables" in A2 2. The user then selects "Cabbage" in B2 3. The user then selects "Fruit" in A2. Granted, your IF statement is handly for making sure that you can't pick a seleciton from B2 UNTIL you've made a seleciton from A2. But it doesn't help with the above scenario, where you in fact DID make a selection from A2, then made a valid selection from B2, but then subseqently changed your selection in A2 making the data in B2 out of context.

Gregoryexcelsemipro.comApril 7, 2013 at 09:48 PM

Your right about that and would need VBA to update the Data Validation upon a change. The worksheet I downloaded from your link on the Excel Web App had all VBA and Data Validation removed.

Jeff Weirwww.heavydutydecisions.co.nz…April 7, 2013 at 09:54 PM

Apologies for that. In short, skydrive sucks. Here's the file on Google Docs: https://docs.google.com/file/d/0B1hgC5lSuLjVYnV5am5fWkRvQmM/edit?usp=sharing

Gregoryexcelsemipro.comApril 7, 2013 at 09:56 PM

Jeff, very nice solution. My hats off to you.

Jeff Weirwww.heavydutydecisions.co.nz…April 7, 2013 at 09:48 PM

Have uploaded file to Google Docs, because it seems you simply can't download it properly off skydrive. https://docs.google.com/file/d/0B1hgC5lSuLjVYnV5am5fWkRvQmM/edit?usp=sharing

AkhilMay 7, 2013 at 05:36 PM

Hey, were we able to solve this Jeff? I am looking at the Macro in the file you attached. but given my lack of proficiency with VBA/Macros I am not able to replicate this in my excel. Any help would be appreciated! Thanks!

AdamMarch 21, 2013 at 06:12 PM

Is it possible to do this if the data contain numbers? i.e. Building/Room 5555 -> - 1 - 2 - 3 4456 -> - 100 - 15 ... without having to precede each number with an underscore? The dynamic drop downs are working fine for me, but all of my buildings start with "_". It works fine like that, but I'm just being picky.

Gregoryexcelsemipro.comMarch 24, 2013 at 02:21 PM

I haven't tried it with numbers, but I can't imagine it work work any differently that using text.

DimaMarch 22, 2013 at 08:36 AM

Hello! Thank you very much for this post, I find it very useful, but I have an additional question: Is it possible to keep hyper-links related to a local network folder or another folder situated in My computer, when using this formula? For example, to be able to open the hyper-link if I select from drop-down list the fruits category (which it's a hyper-link)

Gregoryexcelsemipro.comMarch 24, 2013 at 02:17 PM

I'm not sure how you would be able to do what you are asking. Sorry I couldn't help.

KameronJune 3, 2013 at 09:54 PM

your best choice is to use condition formatting in order to change the underscores to a "invisible" color, whatever matches the background, which is relatively easy to do

John MclachlanMarch 22, 2013 at 01:35 PM

How can you adapt this to password enable ranges in the drop down list. For example: I have a range 1 to 30 to indicate % discounts but any sales reps can grant up to 15% (no password required). Manager1 password can allow additional discount to 20% and Manage2 password allows the full range to 30%.

Gregoryexcelsemipro.comMarch 24, 2013 at 02:16 PM

I'm not sure how you would enable password related ranges. Sorry!

Clarion1March 31, 2013 at 01:28 AM

The conditional list works well however when I copy the sheet into a new tab, the conditional list stops working. The defined names have twice as many items for example my list that applies to the workbook and my list that applies to the new tab. the dropdown however is no longer conditiona. I'm sure there is an easy fix here but I don't know enough to do it. Can you please help?

Gregoryexcelsemipro.comMarch 31, 2013 at 03:21 PM

When you copy a sheet with defined names you get duplication. No easy way around that. If you need more than one worksheet in a workbook, the defined names on each sheet must be different. Excel will usually give you the option to rename the defined names, which is good, but means that you will have to re-do the Data Validation by using the revised name. Sorry I couldn't be of more help.

EmilyApril 5, 2013 at 10:15 PM

This is very helpful. Thanks for posting!

Jeff WeirApril 7, 2013 at 01:08 AM

Great post. Note that you can do away with MyItemList (and hence the the volatile OFFSET function it contains) altoghether by using the below formula in place of your MyItem formula: =INDEX(Table1,1,MATCH(Table2[@Category],Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Table2[@Category],Table1[#Headers],0))),MATCH(Table2[@Category],Table1[#Headers],0)) (and as an added bonus, there's no need to select a particular cell when entering this formula) You can then delete the MyItemList name...it's no longer needed.

Gregoryexcelsemipro.comApril 7, 2013 at 08:03 PM

Thanks for the formula. It appears to be sufficiently long enough to give me a headache just by looking at it. :)

Jeff Weirwww.heavydutydecisions.co.nz…April 7, 2013 at 10:48 PM

Here’s a quick explanation. As you’re aware, you can use the INDEX function to return a cell value from a list or table. For instance, if we want the first row and column of SomeRange, we’d use this: =INDEX(SomeRange,1,1) …and if we want the entire row of the first column in SomeRange, we’d use one of these: =INDEX(SomeRange,0,1) =INDEX(SomeRange,,1) And if we wanted something in-between – say the 3rd row or the 7th row, we’d use theses: =INDEX(SomeRange,3,1) =INDEX(SomeRange,7,1) But INDEX does something cool if you use two INDEX functions with a colon in between them…it returns the entire RANGE that lies between the cell that the first INDEX function points at, and the cell that the second INDEX function points at…inclusive of the first and last references. So for instance this would return an array of all the rows from row 3 to row 7 in the 20th column of SomeRange : =INDEX(SomeRange,3,20) : INDEX(SomeRange,7,20) If SomeRange started in A1, then this is the same as saying: =T3:T20 Given this, we can return a dynamic range that dynamically references a discrete portion of a table using nothing but INDEX. So for instance, say we have an ‘Input’ table with a ‘Master’ dropdown in a column called ‘Master’ and a ‘Slave’ dropdown in a column called ‘Slave’. And we have a table called ‘Database’ that lists the Master categories across the top in the headers, and the various slave categories beneath each Master heading. We can build a dynamic range that references the Slave picklist depending on what the Master picklist is set to. To reference the FIRST cell, we use this: Formula 1: =INDEX(Database,1,MATCH(Input[@Master],Database[#Headers],0)) To reference the LAST cell, we’ll start by first building a reference to the entire column: Formula 2: =INDEX(Database,0,MATCH(Input[@Master],Database[#Headers],0)) And now we’ll need to replace that zero with a count of the number of rows that contain text in the particular column we are referencing. To do that, we just wrap Formula 2 in a COUNTA function: Formula 3: =COUNTA(INDEX(Database,0,MATCH(Input[@Master],Database[#Headers],0))) …and then substitute Formula 3 into Formula 2 where the Zero is: Formula 4: =INDEX(Database, COUNTA(INDEX(Database,0,MATCH(Input[@Master],Database[#Headers],0))),MATCH(Input[@Master],Database[#Headers],0)) So Formula 1 references the first cell of interest, and Formula 4 references the LAST cell of interest. All we need to do now is put a colon between them, and call it a day: Final Formula: =INDEX(Database,1,MATCH(Input[@Master],Database[#Headers],0)): INDEX(Database, COUNTA(INDEX(Database,0,MATCH(Input[@Master],Database[#Headers],0))),MATCH(Input[@Master],Database[#Headers],0))

EricApril 24, 2013 at 12:31 PM

Hi Gregory, Thanks for doing this. I've started using this process this week, but I've come across one issue: in the final drop-down, the contents of the header row is also showing. I'm using Excel 2011 on Mac (which I hate), so there may be something weird happening there. Cheers, Eric

EricApril 24, 2013 at 01:05 PM

Hi again Greg - solved my Header problem. I had named a range for the Table, and then referenced the range instead of the actual Table; hence the automatic header-ing wasn't included. Thanks again!

LindaMay 29, 2013 at 06:36 PM

Hi Gregory, thanks for this very useful post! Is it possible to create a dropdown list that also allows for a list item to have start and end dates? Here's what I'm trying to get in a column called First Review: Scheduled In progress Completed Delayed

Gregoryexcelsemipro.comJune 28, 2013 at 02:48 AM

I'm sorry, but I don't get what you are trying to express to me. It's not getting through my thick skull. Not sure what dates you are talking about.

MattJune 27, 2013 at 03:29 PM

This seems to work great for a two-tier dependent dropdown list, but what about a three tier system? For example, if each item had a list of options to choose from, such as seedless, fresh, rotten, etc. Could that be possible using this method?

Gregoryexcelsemipro.comJune 28, 2013 at 02:46 AM

I haven't done a three tier system as of yet. It gets a little more complicated and I haven't had time to figure it out and write a post.

Functions in this article

Set up the source Table and input cells

Build the category list in Microsoft 365 or Excel 2021+

Name the spill range

Apply the category validation to all three rows

Build a separate item list for each input row

Connect each item cell to its own list

Add an item and check the actual dropdown

When the category changes, clear the old item

The legacy INDEX/MATCH method for older Excel

Define the category and item references

The blank-cell limitation matters

Enjoyed this guide?

Archived comments