Create Dependent Drop-down Lists with Conditional Data Validation

This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down.

Overview

As with just about anything in Excel, there are several ways to achieve the goal. This post explores three such solutions, and if you have a preferred approach, please post a comment, I’d love to hear about it!

** NOTES:

  • Beginning with Excel 2013 for Windows, we can use Slicers as an easier alternative to the solutions presented below. Please check out the Slicers post for more information.
  • Another option is available in versions of Excel that include dynamic array functions and spill ranges. Please check out this post for more information.

The solutions we will explore are:

  • One Table – hardest formulas; easiest ongoing data management; nonvolatile; removes blank choices
  • Two Tables – easier formulas; harder ongoing data management; nonvolatile
  • INDIRECT – easiest formulas; volatile; includes blank choices

The One Table approach stores all drop-down choices (for both the primary and secondary drop-downs) in a single table, but, the formulas are the hardest to set up initially. As a result, once the formulas are set up it is easy to add new primary and secondary drop-down choices without updating the formulas. This is the most “bulletproof” approach, and a good option to use when you have frequent changes to the drop-down choices or when you want to ensure that other users can easily add choices. Plus, you can remove blank choices from the drop-down.

The Two Tables approach stores the choices in two tables, and its formulas are a bit easier to set up initially. Special care must be taken when adding new drop-down choices, including adding new primary choices to two tables and ensuring the secondary table is sorted in ascending order. This is a good option when there are infrequent changes to the drop-down options or when you, the workbook administrator, will be updating the options and can be sure to resort the secondary table.

The INDIRECT approach uses the volatile INDIRECT function. The formulas in this approach are by far the easiest to implement initially, however, the INDIRECT function is volatile which means you may notice an overall performance decrease in your workbook depending on its size, the number of formulas, and so on. This option will include blank choices in the drop down. This is a good option when you want simple formulas and the workbook is relatively small. Note that with this approach, if you change the table name you would also need to update the related data validation formulas. Thanks Danny for your brilliant suggestion here!

We’ll start with my preferred approach, the One Table approach. It is harder to set up initially, but once done, the choices are easier to manage over time. After we go through that approach, we’ll discuss the Two Tables approach. It has easier formulas and is therefore easier to set up initially, but, the choices are more difficult to manage over time. We’ll conclude with the INDIRECT option, which has the easiest formulas.

ONE TABLE Approach

The approach includes three Excel items working in harmony. The first task is storing the data validation choices in a table. The second is setting up named references that dynamically retrieve the correct data validation choices from the table. The third is setting up the data validation cells based on the defined names.

Alright, enough strategy, let’s get into the tactics.

Storing Choices in a Table

First up is storing the drop-down choices in a table. The table feature was first introduced with Excel 2007, so, this approach only works with Excel versions 2007 and later.

The idea is that you store the choices for the first drop-down as table headers, and the choices for the second, dependent, drop-down as table data.

Let’s get an illustration to provide something concrete. Let’s use regions and reps. Our company has four regions, and within each region several reps. We are setting up a worksheet that allows a user to select a region, and then depending on the selected region, a rep. Thus, we need to set up a data validation drop-down that allows the user to select a valid region, and then a second drop-down that allows the user to select a valid rep.

We could store all of the data validation choices, both for the region drop-down and the rep drop-down in the following table, which is named tbl_choices for easy reference:

20140206a

The benefits of storing the choices in a table like this are numerous. First and foremost, it is easy for users to modify the choices. Since tables auto-expand, any new choices added will be automatically included in the drop-downs, including new regions and reps. Column and row sort order doesn’t matter, and the number of choices can vary in each column. One assumption is that there are no blank cells between reps.

Now that the choices are stored in a table, let’s move on to creating the names that will be used by data validation.

Named References that Retrieve the Correct Choices

We have to set up some names that we can put into the data validation dialog box. Thus, these names need to return a valid range reference. Rather than set up unique names for each of the choices, we’ll set up one name to use for the region drop-down, and one name for the rep drop-down. Once the names are set up, they won’t need to be updated, and we don’t need to add new names when a user adds new regions.

The first name is easy, since it simply refers to the headers row of the table. We can use the built-in structured table reference system to create this name.

We’ll use the name manager (Formulas > Name Manager) to create a new name.

Our new name will be dd_regions and will reference the table’s header row tbl_choices[#Headers], as illustrated in the screenshot below:

20140206b

In just a moment, we’ll use this custom name in the data validation for the region cell.

Now, let’s create the name we need for the rep drop-down. In order to set up the name, we’ll need to be familiar with these worksheet functions:

  • INDEX
  • MATCH

Let’s see what we need the formula to accomplish, and then we’ll see how these functions help.

Easy Version

We need a formula that will retrieve the column values for the selected region. This is pretty easy if we want to return all rows, even blank ones. We’ll start with that. After we get that working, we’ll enhance the formula to return only the rows with values, so that the drop-down list doesn’t have a bunch of blank choices at the bottom.

The idea is that we need a formula that will return a column reference based on the table header. If you subscribe to this blog, this should sound familiar. We used a similar technique to dynamically feed a SUMIFS function based on the column header.

Essentially, we use the INDEX function to return a column reference, and we use the MATCH function to figure out which column. We’ll store this formula as a named reference so that it is easy to use with data validation. Assuming that the selected region value is stored in cell C5, which we’ve named region for convenience, we’ll add a new named reference dd_reps that references the following formula:

=INDEX(tbl_choices,,MATCH(region,dd_regions,0))

The INDEX function returns a range reference that starts with tbl_choices (argument #1), includes all rows (argument #2), and includes the column whose header matches the selected region (argument #3, computed by the MATCH function).

A screenshot is shown below for reference.

20140206c

This named reference returns all of the cells in the selected region column, including any blank cells. This is the easy version, and before we move to the advanced version that excludes blank cells, let’s set up the data validation.

Setting up Data Validation

Now that we have the two named formulas, we can simply set up data validation on the region and rep input cells.

The data validation for the region input cell is set to allow a list equal to dd_regions, as displayed below:

20140206d

When we look at the worksheet, we see that it now provides a list of regions, as shown below:

20140206e

So far so good! Now, for the big moment, the conditional rep drop-down, We set up data validation, and we allow a list equal to dd_reps, as shown below:

20140206f

If the region cell is blank, you may receive an error alert message about the name evaluating to an error. You can safely click through the error dialog.

Now, when we return to the worksheet, we notice that the drop-down choices change depending on the selected region…wow…it worked! This is illustrated in the screenshot below:

20140207

And that my friend, is the basic version. It works well, except, I don’t like how the drop-down includes blank cells. It would be better if we could somehow tell Excel to only include cells with values in the drop-down list. And we can, but it gets a little tricky. Let’s explore this advanced version of the formula.

Advanced Version

If you’d like to exclude blank rows from the rep drop-down, we’ll need to enhance our named formula.

Again, there are many approaches to this situation. One possible solution is to use the INDEX function to return a range, as follows.

It is easy to set up an A1-style range reference, you just specify the upper-left corner of the range and the lower-right corner of the range, such as A1:G12, or perhaps B5:D10. The INDEX function can similarly be used. We could use two INDEX functions to specify the range, for example: INDEX(…):INDEX(…). The first INDEX function returns a cell reference that represents the upper-left cell in the range, and the second INDEX function returns a cell reference that represents the lower-right cell in the range.

In order to make the formula we’ll use with data validation easier to understand, we’ll just write a couple of helper formulas and store them as names.

First, we’ll need a shortcut for referencing the selected region column number. So, we set up a new name called dd_col_num that references the following formula:

=MATCH(region,dd_regions,0)

Now, anytime we need to know the column number of the selected region, we can simply refer to it with the name dd_col_num.

Next, we need a shortcut for referencing the selected region column. Not the column number, but, the entire table column. So, we set up a new name called dd_col that references the following formula:

=INDEX(tbl_choices,,dd_col_num)

Now, anytime we need to reference the selected region column, we can simply use dd_col.

Finally, we need a new name to use with the rep drop-down, and so we set up dd_reps2 to reference the following formula:

=INDEX(tbl_choices,1,dd_col_num) : INDEX(tbl_choices,COUNTA(dd_col),dd_col_num)

As you can see, we use the idea of INDEX(…):INDEX(…) to return the range. The first INDEX function returns the upper-left cell, and the second INDEX function returns the lower-right cell. The first INDEX function looks within tbl_choices (argument #1), and returns the cell reference at the intersection of the first table data row (argument #2) and the selected region column (argument #3, as calculated by the dd_col_num named formula).

The second INDEX function returns the cell within tbl_choices (argument #1), at the intersection of the last cell with data (argument #2, as computed by the COUNTA function) and the selected region column (argument #3).

With this in place, we can use data validation to allow a list equal to the name dd_reps2. This time, the drop-down excludes the blank cells, making us very happy.

This is illustrated in the screenshot below:

20140207b

As an alternative to using the COUNTA function to count up the number of cells with values, we could use the MATCH function instead to find the last cell with data. To demonstrate this method, which would include any blank cells between reps, we set up a new name dd_reps3 as follows:

=INDEX(dd_col,1):INDEX(dd_col,MATCH("*",dd_col,-1))

As you can see, rather than try to get at the last rep with COUNTA, this approach uses MATCH instead, by matching a wildcard (*), in the rep column, and -1 for greater than. Check out the Excel help for the MATCH function for more details.

All three of these options, dd_reps, dd_reps2, and dd_reps3 are included in the download file below for reference.

TWO TABLES Approach

The two tables approach uses the same underlying Excel features and functions, including data validation, named ranges, and tables. However, the formulas are easier and thus this approach is easier to set up initially.

A summary of the steps are:

  • Set up a table to store the primary drop-down choices named tbl_primary
  • Set up a named range dd_primary that refers to tbl_primary
  • Set up the primary drop-down input cell with data validation and allow a list equal to dd_primary
  • Set up a table to store the secondary drop-down choices named tbl_secondary
  • Set up a named range dd_secondary that retrieves the related choices
  • Set up the secondary drop-down input cell with data validation and allow a list equal to dd_secondary

Here are the detailed steps.

First, we need to store the primary drop-down choices in a table named tbl_primary:

20140206i

Next, we set up a new custom name dd_primary that refers to the table:

20140206j

Then, we set up data validation on the primary input cell to allow a list equal to dd_primary:

20140206k

The resulting primary input cell is shown below:

20140206l

Now that the primary drop-down is working, let’s tackle the secondary drop-down.

We set up the list of choices in a table named tbl_secondary, which must be sorted in ascending order by the primary column:

20140206m

Next is the named range dd_secondary. This is the only tricky part. We’ll use the INDEX/MATCH functions to figure out the related reps for the selected region. Since our primary input cell is stored in a worksheet named Two Tables in cell C6, we’ll set up the name dd_secondary to refer to:

=INDEX(tbl_secondary[Rep],MATCH('Two Tables'!$C$6,tbl_secondary[Region],0),1):
INDEX(tbl_secondary[Rep],MATCH('Two Tables'!$C$6,tbl_secondary[Region],1),1)

As you can see, this formula uses the range operator (:) to set up the range. The first cell in the range is determined by the first INDEX function. The last cell in the range is determined by the last INDEX function.

Then, we set up data validation on the secondary input cell to allow a list equal to dd_secondary:

20140206n

The resulting worksheet now contains conditional drop-down input cells:

20140206o

 

Multiple Input Rows

As you can see, the above screenshot assumes only one entry, but, what if you needed to set this up so that users can enter multiple rows? No problem, the secret lies in the dd_secondary name.

While named ranges are often set up with absolute references, such as how the dd_secondary name used the absolute reference ‘Two Tables’!$C$6, they can also be set up with relative references. When you set up a name with a relative reference, the active cell matters. That is, the active cell at the time you set up the name will indicate the base location for the relative reference. It is easy to visualize this idea by thinking about normal formulas. When you write a normal cell formula that uses a relative reference, the reference is relative to the cell that contains the formula. The same idea applies when setting up a named reference. The active cell, the one that is selected at the moment you open the new name dialog, will be used to determine how the relative cell reference is evaluated.

We can use this idea to set up multiple input rows. We create the dd_secondary name using a relative reference to the primary input cell while the active cell is the first secondary input cell:

20140206p

Since our workbook already has a name dd_secondary, we’ll use dd_secondary2 instead, and set the name equal to:

=INDEX(tbl_secondary[Rep],MATCH(Multiple!B7,tbl_secondary[Region],0),1):
INDEX(tbl_secondary[Rep],MATCH(Multiple!B7,tbl_secondary[Region],1),1)

You’ll notice in this version of the name, the MATCH function finds the value on the Multiple sheet in cell B7, which is a relative reference. Since the active cell when we set up the name was C7, the name will use the value to the left.

Now, when we set up data validation for the secondary input cells, we allow a list equal to dd_secondary2.

This is demonstrated in the sample file on the Multiple worksheet.

INDIRECT Approach

In this approach, we set up one table to store the primary drop-down choices in the header row and the related secondary choices in the columns. For illustration purposes, assume the table is named tbl_reps.

In the Data Validation list source box for the primary dropdown, you would use this:

=INDIRECT("tbl_reps[#Headers")

This tells data validation to convert the text string “tbl_reps[#Headers]” into the corresponding table reference to the header row. Please note that since the table name is embedded in the formula, if you change the table’s name you would need to also update the data validation formula.

Assuming the primary drop down is stored in A1, you would then use the following formula in the secondary drop down’s data validation list source

=INDIRECT("tbl_reps["&$A$1&"]")

The INDIRECT function here tells Excel to convert the text string into the structured table reference for the column name identified in A1.

The INDIRECT function is volatile, which means that it is updated each time the workbook is recalculated. This can be a performance hit in large workbooks. But, in small workbooks you shouldn’t notice any performance hit and should work just fine.

Thanks Danny for this clean alternative solution!

 

This approach is demonstrated in the ConditionalDropDown3b workbook below.

Conclusion

The One Table and Two Tables approaches are two ways to provide dynamic drop-downs using Excel’s data validation feature. If you have a preferred approach, or a way to simplify the formulas, please post a comment below!

Additional Resources

  • To download the Excel file used for the screenshots above, which includes the table, named formulas, and data validation cells: ConditionalDropdown
  • Multiple input rows example: ConditionalDropdown_2
  • Using the INDIRECT approach: ConditionalDropdown3b
  • Obtain the secondary drop-down list from a filtered PivotTable: ConditionalDropdownPT
  • If you need to go more than two levels, you could simply continue this strategy and create drop-down choice tables for each additional dependent drop-down.
  • If you haven’t explored named references, tables, or data validation, these are all topics covered in the Excel University Volume 1 book and online Excel course.
  • INDIRECT function to pull values from related tables

 

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

149 Comments

  1. Chetan Karia on June 17, 2014 at 10:20 pm

    Thanks for the above example which has helped me a lot…
    I have applied this to amy excel. Actually i have to apply it to my full row and simultaneously the dependant drop down row.
    But when i select the drop down in second row of my primary drop down, the dependant drop down gives me the answer of first primary drop down in the 1st cell.

    Please suggest me a help.

    Chetan Karia

    • jefflenning on June 19, 2014 at 7:46 am

      Chetan,
      It sounds like your secondary (dependent) dropdown contains the same list of choices as your primary dropdown. In that case, I would double-check to ensure that your data validation source field refers to the correct name, which in the example above would be dd_reps (not dd_regions). Hopefully, this will solve the issue, but if not, then I would next double-check the formula for the name to ensure it uses the correct functions to retrieve the table column rather than the header row.
      Hope this helps!
      Thanks
      Jeff

  2. brad on June 25, 2014 at 1:31 pm

    Before I start attempting too many things I wanted to ask a question.

    So I have specific headers for certain files I upload. They differ, so first column could be shoes, but then shoes will be the third column on another file.

    Can I use these tips to make a blanket template so where, no matter where the header “shoes” is, it will drop down my options? I messed around with your file that I downloaded and it doesn’t work like how I’m wanting it to act.

    I already use a vlookup/match formula that works create when filling in the information and trying to get the same thing but only allowing the data validation choices.

    Hope this makes sense !

    • jefflenning on June 27, 2014 at 10:49 am

      Brad,

      The approach demonstrated in this post works regardless of the table’s column order. To confirm, download the file and then re-position the table columns and the data validation dropdowns continue to work as expected.

      If I misunderstood your question, and you are not referring to the table’s column order, please reply back, happy to help!

      Thanks
      Jeff

  3. Rebecca on July 16, 2014 at 11:13 pm

    Hi Jeff,

    Great post – this is exactly what I’m trying to do. One question…is it possible to blank out the Rep selection if the end user changes the Region? e.g. JAB is assigned to North Region.

    Step 1, I select Region = North, then Rep = JAB, no problem.
    Step 2, I change Region to East (valid results for East region= HAP, NEO, VRM). JAB – i.e. old value selected in step 1 is retained.

    Is it possible to blank out JAB or force the user to change it?

    Thanks in advance

    • jefflenning on July 17, 2014 at 9:22 am

      Rebecca,

      To my knowledge, there is not a built-in way to accomplish this task. Essentially, we are trying to ask Excel to change the stored value of one cell (set it to blank) when the user changes the stored value of another cell (the previous rep selection).

      Probably my favorite alternative would be to conditionally format the rep input cell when the rep doesn’t appear in the current region selection’s rep list. For example, apply red conditional formatting to the rep input cell if the rep doesn’t appear in the rep list of the current region. To do this, you would set up conditional formatting on the rep input cell, and “Use a formula to determine which cells to format”, and then use a formula similar to the following:

      =ISERROR(MATCH(rep,dd_reps,0))

      This formula returns TRUE when the current rep does not appear in the current region’s rep list. You could set the format to red to make it clear to the user that the current rep doesn’t appear in the rep list.

      But, to accomplish exactly what you are asking, we would need to turn to macros. A macro can monitor the changes in specific cells, and we could ask the macro to change the rep input cell to blank anytime the value in the region input cell changes. So, this macro approach is probably the one to pursue to achieve your goal if the conditional formatting option isn’t preferred.

      Hope this helps!

      Thanks,
      Jeff

      • Hameed on February 13, 2017 at 8:24 am

        Thanks for this, quite useful and wonderful

      • Jorge Posada on November 3, 2017 at 10:58 am

        You can actually simulate what Rebecca is asking by using your conditional formatting formula and specifying a white fill and white letter color.

  4. Roman on July 21, 2014 at 9:52 am

    Jeff, thanks for this great post. I want to have these drop-down lists copied all the way down in my dynamic table and when I add a new row below, these two drop-down lists would be auto-expanded. My problem is that the secondary drop-down list in the rows below the first row displays choices depending on the value selected in the primary drop-down in the first row. Please help. Thanks.

    • jefflenning on July 24, 2014 at 12:09 pm

      Roman,
      As far as I can tell, you are placing the two drop down cells on top of each other, vertically. If possible, it may work better to place them side by side, horizontally. The named range used in the secondary data validation would need to reference the relative cell reference of the primary cell. That way, when new rows are added to your table, the relative reference ensures it will reference the new row.
      Hope this helps!
      Thanks
      Jeff

  5. Kumaran on August 3, 2014 at 11:25 pm

    I need a dynamic drop down list based on the value i enter or select.

    This is for postal pincode i we enter or select…. and i need list of “post office” for that pincode. The postal pincode list have “pincode” in first column, “Post office” name in second column… like below.

    PINCODE OFFICENAME
    621714 Nakkambadi B.O
    621714 Nallampalayam B.O
    621714 Neivanam B.O

    • jefflenning on August 7, 2014 at 12:11 pm

      Your postal code / office name dropdowns can be accomplished using the approach presented in this post. You’ll first need to convert the format of your data from being displayed in two columns to a table, where the header row is postal codes and the columns are the office names. Once the data is stored in this format, you can apply the method discussed above. Best of luck, sounds like a fun project!
      Thanks,
      Jeff

  6. Tyler on August 5, 2014 at 1:00 am

    Thanks for this post! I am trying to learn advanced Excel features on my own so it was very educational. I wonder if you could help me solve one issue I am having though.

    In your example you referenced a specific cell named region but what about when I want to continually reference the cell to the immediate left in a a table? I tried the following and it works so long as I do not further try to dead space in the second list. (It works although does warn that it evaluates to errors)

    =INDEX(Table2,,MATCH(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),dd_colleges,0))

    • jefflenning on August 7, 2014 at 12:50 pm

      In order to use multiple dropdown rows on a single sheet, the key is that you select the first dependent dropdown cell, and then set up the name using a relative cell reference to the primary dropdown cell. Also note that you may get an error when setting up the secondary dropdown named reference if the primary dropdown cell is empty. Just click through the error.

      For example, if the first input row on the Input Sheet was row 7, and the primary dropdown is in column B and the dependent dropdown is in column C, then, you would select cell C7 first and then set up the dd_reps named reference:
      =INDEX(tbl_choices,,MATCH(‘Input Sheet’!B7,dd_regions,0))

      I’ve added a new download file above, named ConditionalDropdown_2, that demonstrates this approach.

      Hope this provides the information you were seeking…good luck!

      Thanks
      Jeff

      • Nick on August 20, 2014 at 5:50 am

        Hi Jeff,

        This was exactely what I was looking for, so thanks!
        I was able to use your formulas and got the same result as in your file ConditionalDropdown_2.
        However, I still have a lot of blanks in my conditional drop down list and was wondering if you know how to fix this?
        If I use DD_reps 2 or 3, I get the same problem as Chetan described.
        A1 contains my ‘regular’ dropdown list and B1 contains the dependent one.
        Changing the value in A1, changes the dropdown list in B1, so this works just fine.
        When I copy the cel A1 to A2, that goes as well, but when I copy B1 to B2, B2 shows the same dropdown list as B1.
        Basically my question is: can you get rid of the blanks in the dependent dropdown in your file ConditionDropdown 2 an if so, can you tell me how?

        Thanks in advance,

        Nick

        • jefflenning on August 27, 2014 at 7:27 am

          Nick,

          Ah…thanks man…I see that I used the version of the formula that results in a secondary dropdown that includes any trailing blank rows. I have added a new worksheet named Input Sheet No Blanks which uses the version of the formula that removes the trailing blank rows from the secondary dropdown. This new sheet has been included in the revised download link above (ConditionalDropdown_2).

          Thanks
          Jeff

  7. Drostan on August 19, 2014 at 12:42 am

    Thanks for this post it had been of tremendous help for a document I am working on.

    To perfect my document further (make annoying data entry even easier and quick) I have one further question

    I have a couple of range (list) with only one item in it (In your example one rep having charge of a whole region on his own)

    Is it possible to set it so that it would in this case fill automatically the cell with the only one possible option and propose a list when there is 2 item or more in the list?

    Thank you again for your tutorial here

    • jefflenning on August 25, 2014 at 7:59 pm

      Drostan,

      I believe that you are asking if the dependent dropdown cell can automatically be populated when it contains only one choice. The short answer is no, the data validation feature does not support this behavior, however, a couple of ideas come to mind. You could simulate this by setting up a default column that uses a formula to populate it, and then provide another user override input column for the user to make a selection when the default is incorrect. A third column is a simple formula that returns the override value if not zero, otherwise the default. The other idea would involve VBA, but, that could get a bit tricky, so probably best to start with the non VBA option first.

      Thanks
      Jeff

  8. krishnaprasad on August 25, 2014 at 12:27 am

    dear sir,

    while creating ONE TABLE APPROCH i have a problem while naming table one error as occur can you explaine in details how to create table

    regards

    krishnaprasad

    • jefflenning on August 27, 2014 at 7:04 am

      Krishnaprasad,

      To create a table, which is a feature first available in Excel 2007 for Windows, you use the Insert > Table command icon. To name the table, you want to use the TableTools > Table Name ribbon field. Hope this is the information you were seeking…thanks!

      Thanks
      Jeff

  9. Pranav on August 31, 2014 at 11:18 pm

    Was looking for the exact same formula requirement – got it here and its workin perfectly well in first shot.!! Cudos to the author for providing a detailed and well-explained piece here on this.

  10. Shikha on September 3, 2014 at 8:31 pm

    Hi Jeff,

    Very useful information. Thanks for sharing this. I implemented the two tabled approach multiple row in my project but then I faced one issue. I have a scenario where the primary field has text data that has ~ sign in it.For example text~1. Now the dd_secondary2 logic is failing (INDEX(tbl_secondary[Rep],MATCH(Multiple!B7,tbl_secondary[Region],0),1):
    INDEX(tbl_secondary[Rep],MATCH(Multiple!B7,tbl_secondary[Region],1),1))
    probably this fails to read ~ sign .
    Please suggest what to do in such case

    Thanks in advance
    Shikha

    • jefflenning on September 4, 2014 at 7:07 am

      Shikha,

      In the current MATCH function, the tilde ~ is a reserved character, so, the current formula is not being interpreted properly by Excel. One option is to wrap the SUBSTITUTE function around the MATCH function’s first argument. The SUBSTITUTE function will find a character and replace it with another. If we do it with the SUBSTITUTE function then no data changes are needed. We want the SUBSTITUTE function to replace the single ~ with two ~~. The idea is to have the MATCH function find text~~1 instead of text~1. The MATCH function will interpret text~~1 as text~1 during the formula evaluation.

      Instead of MATCH(Multiple!B7,tbl_secondary[Region],0)
      We would use MATCH(SUBSTITUTE(Multiple!B7,”~”,”~~”),tbl_secondary[Region],0)

      Hope this helps…thanks!

      Thanks
      Jeff

  11. Syam Issac Thomas on September 8, 2014 at 1:16 am

    I Have a doubt, hope you can help me. For example i have a drop down list of different models of Televisions, then i have cells where different details of Televisions to be displayed like Screen size, required power, rated voltage etc.. so when i select a model from the drop down list, then there is any way these details will automatically displayed on their cells.

    • jefflenning on September 16, 2014 at 9:01 am

      Syam,
      Yes, this can be achieved with the VLOOKUP function. Assuming the drop down contains the unique television model ID in cell A1, you could use the following VLOOKUP function in a cell such as B1 to retrieve say the screen size which may be stored in the second table column:
      =VLOOKUP(A1,Table1,2,0)
      To retrieve the value in the 3rd column, which may store the required power, you would use:
      =VLOOKUP(A1,Table1,3,0)
      Then, when the user selects the television model ID, the VLOOKUP formulas retrieve the related attributes.
      Hope this is the information you were seeking…thanks!
      Thanks
      Jeff

  12. Cole Sutera on September 10, 2014 at 12:11 pm

    Hi,
    Currently, I have a drop down menu that references a table/range. Then I have lookup formulas to the right that display what’s in the row. However, I have multiple tables. So, I made a separate drop down row for each table. What I would like to do is make a drop down list that identifies the tables and then makes available the drop down list to identify the rows in the chosen table. Is there a way to do this?

    • jefflenning on September 16, 2014 at 8:29 am

      Cole,
      Yes, absolutely. If you build each of the dependent tables with the same column name, this will be pretty easy with the INDIRECT function. Essentially, you set the secondary drop down named reference equal to a formula that uses the INDIRECT function, which builds a valid Excel reference from a text string. Assuming that your primary drop down cell is D2, and it stores the desired secondary table name, and assuming your column name in all related tables is ID, then the following formula used as a named reference would work for the secondary data validation drop-down:
      =INDIRECT($D$2&”[ID]”)

      The INDIRECT function creates the correct structured table reference by joining the value in D2 (the table name, such as Table1) and the column reference (such as [ID]).

      I’ve uploaded a new Resource above named INDIRECT.xlsx which demonstrates the technique.

      Thanks
      Jeff

  13. Andy Williams on September 27, 2014 at 4:41 am

    Hi,
    I am trying to create a weekly schedule/timetable which shows subject and topic. The subject name will allow limited topics in that area to be selected. It also needs to show consecutive weeks in columns and allows the identical type of selection.

    I have used the single table method and have got the first column to work correctly, but subsequent columns (weeks), no matter what subject is selected always refer back to the subject selected in the first column, and therefore gives me the wrong choices.

    Any ideas why this is?

    The idea is to then allow all topics to be selected e.g. identical groups in rows below. so for example you could have all teachers input their topic and subject for each week (vertically) and adjust for consecutive weeks (horizontally). Reading post above I am assuming that simply copying and pasting the rows will create an issue. Any pointers before I attempt that would also be gratefully received.

    • jefflenning on October 2, 2014 at 10:12 am

      Andy,

      It sounds to me like the issue lies with the named reference. Be careful to set up the absolute/relative cell references properly. Select the secondary drop-down cell for the first one, and then set up a new name. In the new name dialog, be sure that the “refers to” formula uses a relative column reference, such as B, rather than an absolute column reference, such as $B. That way, it can be used in multiple columns to refer to the value in the column to the left (relative), rather than in the original column (absolute).

      Hope it helps!

      Thanks
      Jeff

  14. AM on October 30, 2014 at 11:36 am

    Hi, thanks the TWO TABLE approach was extremely helpful!

    At the moment, I am building a Dashboard that populates from these two drop downs. The two issues I’m having are:

    1.) When selecting a new item from the primary drop box again, it does not always match the one that is left from the last selection in the dependent box (e.g., Fruit in primary, and “Carrot” in dependent, still there from last time you had selected vegetables). So I’m thinking maybe I should CLEAR the contents of the dependent drop down WHEN the top box is clicked on again. Not helpful when don’t match correct types, and it looks bad when the Dashboard says “FALSE” everywhere becuase I’m using IF(AND(…

    2.) I’d like to name the drop downs with apostrophe’s but I cannot without the second box not working (e.g., I seem to have use Bachelors instead of Bachelor’s) (I’m making a dashboard for college level data)

    Any help would be appreciated!!
    Thanks again!

    • jefflenning on November 7, 2014 at 11:21 am

      AM,

      Hi there! Glad the two tables approach was helpful!!

      Regarding your questions:

      1) How to auto-clear the dependent cell value. To my knowledge, there is not a built-in way to have Excel automatically clear the dependent cell when the user makes a selection for the primary cell drop-down. Although, you could explore using a VBA macro to accomplish that if needed.

      2) How to use a value with an apostrophe. In my testing here, I updated the values in both tables to include apostrophes, for example, in the sample file attached to the post, I changed North to North’s and South to South’s. The drop-downs still worked as expected. I think the key is to ensure that the apostrophe is added to both tables, not just the primary table.

      Good luck on your college data dashboard!!

      Thanks
      Jeff

  15. Maliek Washington on November 6, 2014 at 8:45 am

    Jeff,

    Thanks so much for the helpful information! Perhaps you can help me with my issue, which is pretty simple in nature.

    I made a spreadsheet with drop down boxes to restrict choices to product codes. (It’s a product catalog and rather than risk entry errors, we went with the drop down box) There are no dependencies involved and I have the data on a separate worksheet tab.

    I have 2 columns in my data set: Product code & Description. I’d like for the respective data in the description column to populate in an adjacent column when the user chooses the product code from the drop down.

    This makes sense to me as I read it and I hope it translates. 😉

    Thanks in advance.

  16. David on November 20, 2014 at 10:21 am

    This was very useful, thanks!
    David

  17. Todd on February 4, 2015 at 9:27 pm

    Hi,

    I am trying to create a spreadsheet that has three drop- lists based on multiple tables of information. The first is basically a list of states. The second is a dependent list of the first and creating a list of customers within that state. The third is also a dependent list based on the second that lists the locations of a customer within the state. Currently, I have three tables of data.

    So far I have created the first two, with the latter being a dependent list using the INDIRECT function. However, using the INDIRECT function for the third would mean over 500 separate lists that would need to be created. Far too time consuming.

    For my third drop-down, I have input all of the formulas listed in this tutorial and tried a number of different ways to execute this, but the output values only represent the locations of the first customer within any given state. No matter how I change the formula, it will not change to the correct output values.

    Any suggestions???

    • jefflenning on February 6, 2015 at 1:16 pm

      Todd…I’ve got a great approach for you. It is to use slicers instead of data validation. I’ll write about it in my blog post next week to demonstrate the details and steps. It will be much easier to manage.
      Thanks
      Jeff

  18. Hannah Frampton on May 11, 2015 at 6:45 am

    Hi Jeff,
    I have an analysis table showing responses from suppliers on certain questions. I need to create a form that shows their answer (ive done this with a drop down) and then an automatic suggestion depending on this answer.
    I hope that makes sense. Would the above do this for me?
    Thank you

  19. Ryan Steer on June 5, 2015 at 4:44 am

    Hello Jeff,
    I have been looking for a way to do this exact thing for a long time. I was excited when I found your post and I thought all of my problems were about to go away, but I ran into one major issue. I am trying to apply this to columns of a table. I need a way to have a drop down of the headers (dd_regions) in one column and then a drop down of the list under the headers in the next column (dd_reps).

    Every time I try to do this the “region” name value stays as the first value in the column. Is there a way to define that name to reference the row that is selected? The only way I could think of to get this to work was to have a separate name for each row, but this is a table that will continually grow as data is entered and I wouldn’t be able to keep up with that.

    Please give me your thoughts. Keep up the quality posting!

    • jefflenning on June 12, 2015 at 9:01 am

      Ryan,
      First, to confirm, I believe you have two tables, one table contains the list of choices (the primary drop down is based on the headers row, and the secondary drop down is based on the selected column values) and the other table is the input table, where the user is entering data and where you want your drop-downs. The input table’s first column will contain a drop down that is the same for all table rows, and it refers to the choice table’s header row. The input table’s second column will contain a drop down that is different for each row, depending on the selection made in the first drop down. So far so good? If so, then, here is probably where the issue lies: the active cell matters. Let’s walk through it.

      The first drop down is easy, because the list of choices is equal to a name that points to the choice table’s header row, such as region refers to Table1[#Headers]. The second drop-down is easy, but, the name for the list of choices has to be set up based on the active cell. That is, when you create the name, such as reps, your active cell matters. You want to select the cell in the input table’s second column where you’ll eventually add the data validation list, and then when you are writing the named formula, be sure to use a relative cell reference that points to the value in the cell to the left. Be sure that the cell reference is either the structured table reference (Table2[@Primary]) or an A1-style reference without dollar signs (B7 vs $B$7). The cell that is active at the time you open the name manager to create the new name matters, and, any cell references used in the name are relative to the active cell.

      I think that is the missing link…give it a try and hit me back if needed…happy to help!

      Thanks
      Jeff

  20. Robert Pearce on August 4, 2015 at 3:07 am

    Hi,
    Your tutorial worked out well for me however I seem to have a problem when I try to move my worksheet file locations, I wish to use the workbook with tables and data as a seperate database which is read to another able with the drop down menus. However when I move the file locations from my pc they no longer work, do I need a different reference in the Name manager?

    • jefflenning on August 10, 2015 at 8:16 am

      Robert,

      When you create a reference to an external workbook file, the full path to the file is stored in the workbook. As you have noted, this means that if you move the file locations, the references will break. Using named references won’t help in this case. In practice, I will typically address this type of situation by either combining the workbooks so that all of the data resides in a single workbook, or, store my data in an Access database and retrieve it with the External Data feature of Excel. I am careful to not move the database location around.

      Having said that, if you are required to reference data in another workbook file and you must be able to move the file locations around, you may want to explore the INDIRECT function which enables you to create a valid Excel reference from a text string. You can then enter the current file path into a cell such as A1 and then build the reference via the INDIRECT function such as =INDIRECT(A1&”B1:D20″).

      I hope these ideas help!

      Thanks,
      Jeff

  21. Nick Harris on August 18, 2015 at 11:30 am

    Hi,

    You have been absolutely a great help. I implemented the one table method with great success. – Thank you.
    I have one issue, how do I make multiple drop downs (say some sort of Order Sheet) where first column is product category and then SKU.
    I am going to have 50 of these double conditional formats. Is there anything I can do to minimize my named ranges and not have 50 dd_reps, 50 col_num etc…

    Thanks!

    • jefflenning on September 3, 2015 at 9:01 am

      Yes…no worries. The key to making this work is to be careful to use relative cell references when defining the secondary drop-down named reference. And, the key to using relative cell references is to realize that at the time you open the New Name dialog, the active cell is considered the anchor cell. For example, if you select cell B1, and it is the active cell when you open the New Name dialog, the cell reference you use to create the name is based on cell B1. So, a reference used in the New Name dialog such as A1 actually tells Excel “value in the cell to the left.”

      By using a relative reference when you create the secondary drop-down name, you can use this one version of the name without needing to create 50 names.

      Check out the ConditionalDropdown_2 file which demonstrates this technique.

      Hope this makes sense and hope it helps…good luck!

      Thanks
      Jeff

  22. fattah heisenberg on August 25, 2015 at 10:23 pm

    Jeff.
    Thanks for the method, I’ve looking for the most easy way for long time and google has direct me to this.

    kudos for you!

    BR
    gr8 Heisenberg

  23. Elijah-Jack on August 27, 2015 at 12:42 am

    Hey Jeff,

    Right. I am trying to do a drop down list which is “conditional”. in say field A1 you input number of people, I would like a drop down list that then gives options that refer to the amount of people.

    That might not be brilliantly explained. So we have different layouts where people sit. different layouts require different space and equipment and have limits on the amount of people they can seat. So there might be a limit of 18 people on one layout so if there are 19 people then I want this list to not have layouts with a max of 18 on it.

    I hope you can help.

    Thanks,
    Elijah

    • jefflenning on September 3, 2015 at 8:33 am

      Elijah-Jack,
      A couple of ideas come to mind, and probably the easiest one to implement is to have your user select a range rather than input a specific number. For example, instead of allowing your user to enter 5, or 18, etc, have them select from a drop-down that includes the ranges, choices such as 0-5, 5-10, 10-20, etc. If you require the exact number of people, instead of just the range, then, I would have your user enter a specific number of people into a cell, such as A1, and then have a formula that populates another cell, say A2, with the relevant range 0-5, 5-10. You could store these in a table and retrieve the range with a lookup function such as VLOOKUP. Then, your secondary drop-down would be based on the range determined via formula and stored in A2.
      I hope these ideas help…ping me back if needed…thanks!
      Thanks,
      Jeff

  24. Brittany Hall on August 28, 2015 at 12:05 pm

    I’m missing a huge step here and I’m not sure how to articulate what I need. I’ve named the table, named the headers, but can’t figure out how to name the range of sub categories for the dependent drop down. My first data validation for the headers works great, but the dependent one says there’s an error.

    I feel like I’m missing a step.

    • jefflenning on September 3, 2015 at 8:25 am

      Brittany,
      No worries, I’m happy to help. I’d recommend getting started with the INDIRECT approach. To do so, store the primary drop-down choices in a table. Then, store the secondary choices for each primary in their own table. The key, which may be the missing step, is to name each secondary table using the Table Tools > Table Name field with the exact name of the primary choice. There are rules for naming tables, such as they can contain no spaces or funky characters, meaning, your primary choices need to have simple names like AccountNumber instead of Account Number. Then, for the data validation rule on the secondary drop-down, you use =INDIRECT(A1) where A1 would be the cell of the primary drop-down choice.

      I hope this helps you get it working…thanks!!

      Thanks
      Jeff

  25. Виктор on September 1, 2015 at 10:01 pm

    By using the INDIRECT function, you can then create additional drop-down lists that are conditional to the first drop-down list.

  26. Peter on September 15, 2015 at 4:16 pm

    Rookie here, just wondering how I could use data validation to create a drop down list, and that each option would have a value attached…
    e.g. Small Business – 3 options of product (Sm, Med, Large), Cost is ($1, 2, 3 respectively)

    I’d like to be able to use DV so that in my drop down menu I select Med, and then in other cells it will recognize this cell as having a value of “2”

    Can you help?
    thanks

  27. Arth Vince Malaca on September 15, 2015 at 11:57 pm

    Hi Sir,

    Thank you for that wonderful presentation. I would like to ask if you could help me with my problem. I am currently creating a progress monitoring report and I have data which looks like this:

    On sheet 1 Column A, let’s say I have the name of my staff

    AAA
    AVM
    RGR
    AVM
    AAA
    AAA
    RGR

    On column B, their submitted reports like this

    Report 1
    Report 2
    Report 3
    Report 4
    Report 5
    etc.

    On sheet 2, I have a dropdown cell for Staff and a dropdown cell for Report.

    What want to do is when, for example I chose Staff AAA, I will only see all the Reports of staff AAA to the Report Dropdown.

    Thank you for your time

  28. Steve Brown on October 1, 2015 at 4:53 pm

    Hi Jeff,

    Thanks for very helpful technique.

    I would like to extend this if possible. I am using your Two Table solution for a list of product categories as first dropdown and then the related products as second dropdown. In the product table is a column named Active with either a “Y” or “N”, depending on our supply and sometimes seasonal.

    How can I make the second drop down dependent on two values: the value of category in the first drop down and the value for the product being equal to “Y) in the Active column of the Product table?

    Thanks in advance for any help!

    • jefflenning on October 8, 2015 at 12:09 pm

      Steve,
      One way to accomplish this is by pulling your drop-down list from a PivotTable instead of directly from the Table. The workflow is basically that you’d update your Active status in the table as needed, then, refresh the PivotTable (which would be set up to show only items where Active=TRUE). Then, the name for the secondary drop-down pulls values from the filtered PivotTable instead of the Table. I’ve uploaded a new sample workbook to demonstrate this technique…it is called ConditionalDropdownPT.
      Hope it helps!
      Thanks
      Jeff

  29. Steve Brown on October 10, 2015 at 10:17 am

    Jeff,

    Thanks so much for the sensible suggested solution and example file for the problem of a conditional drop down based on a filter value embedded in the secondary lookup table. This is a great help – you are truly an invaluable resource for the Excel community!

    Thanks again so much!!,
    Steve

    • jefflenning on October 10, 2015 at 6:05 pm

      Thanks for your kind comment…I’m happy to help!

  30. Dragan on October 14, 2015 at 12:07 pm

    Hi Jeff,
    I have a task to create Drop Down menu with 3 steps included:
    A.Chose a product,
    B.Is packaging required,
    C. If yes-what color.
    Based on choices in all 3 drop down menus, I would need to automated way to copy/paste Bill of material that is already designed on 2nd sheet.
    So, as example: Product A/Needs Packaging/Blue color= Bundle 16 (to be copied just below Drop Down menu).
    How to accomplish this?

    Thank you in advance.

    • jefflenning on October 30, 2015 at 11:16 am

      Dragan,
      Assuming that the choices in each of the drop-downs are independent (and the choices in one don’t depend on the selection of another), then, you would just create the drop-downs using data validation. Then, you would use a formula to retrieve the bundle from the second sheet. Since there are multiple conditions in the lookup, I’d recommend going with a SUMIFS function. But, SUMIFS is limited to returning a number only, so, if you are ok to return the number only, like 16, and not a text string, like Bundle 16, then SUMIFS would be perfect. However, if you need to return a text string, such as Bundle 16, then you would want to use VLOOKUP combined with SUMIFS. I’ve actually written some other posts that may assist:
      https://www.excel-university.com/multi-column-lookup-with-vlookup-and-sumifs/
      https://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/
      Hope these help…and best of luck!
      Thanks
      Jeff

  31. André SG on October 22, 2015 at 6:11 am

    Thank you. Very helpful

  32. Mohammad on January 1, 2016 at 3:11 pm

    Hi thanks for your pretty Site!
    actually i have created two drop down List very simple just by using a HLOOKUP Function. the HLOOKUP Function Creates(chooses) a Column of a table Based on what is selected in first drop down. and the second drop down take it’s items from the changing column and updates automatically.
    i can email the workbook if you wish

    • Kurt LeBlanc on July 27, 2016 at 10:01 am

      Hey Mohammad,

      Thanks for your additional information!

      Kurt LeBlanc

  33. Nilce Alves dos Santos on January 29, 2016 at 11:24 am

    Thank you very much! This post and a couple of others written by you were really essential for me to create a good simulation spreadsheet. Your texts are very didactic and helpful.

    • jefflenning on January 29, 2016 at 11:28 am

      Thanks, I’m happy to help 🙂

  34. Joeri on March 16, 2016 at 2:48 am

    Hi Jeff,

    I use the ‘one table’ approach, including the method that removes the blanks, which works perfectly. However I’m trying to get my input value (region in your example) not from a fixed cell, but from a column in an Excel Table, so basically using your example my table would contain the 2 columns Region and Rep, and for each row I want to have the dropdown (datavalidation) like in your example. I can get the dd_region to work ofcourse, the dd_col_num formula also works, when I replace “region” with the actual cell and not lock it with $ : =MATCH(C3,dd_regions,0). It gives the correct value for each row based on dd_region.
    If I then add data validation, I get it to work with dd_col, but that includes the blanks ofcourse. However the dd_rep2 formula keeps giving me an error (nothing specific just “… results in an error” in the name manager).

    Any idea’s how to fix this ? Main issue is your metodology starts from “region” with is a fixed cell and mine is a value in a column that changes each row.

    Thanks a lot!

    Joeri

    • Kurt LeBlanc on October 25, 2016 at 10:30 am

      Hey Joeri

      Can you provide me with the formula you are trying for dd_reps2? I keep reading your comment and the blog post to fully understand it and it must be something syntactically. I need your formula and the formula for any names it uses.

      Thank you,
      Kurt LeBlanc

  35. RM on May 18, 2016 at 7:32 am

    Hi there, this explanation seems extremely valuable. however, I’m having trouble with my “match” statement. I think it stems from this portion of the walkthrough:
    “Assuming that the selected region value is stored in cell C5, which we’ve named region for convenience” So I get that you named “c5” as “region” but I don’t follow what data is compiled in C5? what would the formula look in c5? This is where my Match statement is failing. Thanks

    • Kurt LeBlanc on August 4, 2016 at 10:02 am

      Hey Robert

      C5 and the other input cells are drop-down lists. They don’t technically hold any formulas. You may be referring to the data validation rule that has a formula for the list it allows. If you select the cell and click on data validation in the Data tab, you’ll see the rule for that cell:) I recommend looking at the Name Manager in the Formulas tab to see what names the rules use refer to.

      Let me know if I can help you further:)
      Kurt LeBlanc

  36. RM on May 18, 2016 at 8:22 am

    Hi there, this explanation seems extremely valuable. however, I’m having trouble with my “match” statement. I think it stems from this portion of the walkthrough:

    “Assuming that the selected region value is stored in cell C5, which we’ve named region for convenience” So I get that you named “c5” as “region” but I don’t follow what data is compiled in C5? what would the formula look in c5? This is where my Match statement is failing. Thanks

    • Kurt LeBlanc on July 5, 2016 at 5:51 am

      Hey Robert,

      The region is just an input cell. It’s used in the MATCH() formula to find the number in the sequence of column headers, which returns a number for the column to look in to the INDEX() formula. I hope that’s a clear explanation:)

      Let me know if I need to reword it for you. I’m happy to help!
      Kurt LeBlanc

  37. Riz on June 6, 2016 at 7:12 am

    Hi Jeff, I need a little help from you, if you could.

    I have prepared one excel sheet, which has so many dropdown lists. Each dropdown is dependent on the previous one for accuracy and to minimize the data entry. But, this has tremendously increased the file size to 6MB. I am looking a way to reduce the file size but didn’t find any solution yet.

    Second option is to keep all the name ranges data in a different workbook and keep the main data in different workbook, which works well and reduces the size of main sheet, but then I am not finding a solution to pick the data in dropdown list from another workbook which support dependency. For example: I have fields, Date – Department – Equipment – Resource, All these fields are dependent to each other. If I select Department as Technical, it should show me Equipment list and related resource from that department, if I select Department as Operations, it should show me another list with relevant resource. But again how to pull the data from an external worksheet. Currently, all the name ranges are in the same workbook but in different worksheet and works very well.

    Any idea how to handle this situation?

    • Kurt LeBlanc on July 1, 2016 at 8:17 am

      Hey Riz,

      I think my suggestion will help you with both issues…The External Data Connections feature in Excel allows you pull data from outside sources, and you can change the refresh time as you need.

      Let me know if this helps you:)
      Kurt LeBlanc

  38. Ray on June 25, 2016 at 9:40 am

    I’m trying to get the two table approach to work. I guess I don’t understand the formula well enough because I’m getting the error message “There’s a problem with this formula. Not trying to type a formula…”. The formula I’m using is:

    =Index(Table_Product[Product],Match(Menu!$F$2,Table_Product[Commodity Group],0),1):Index(Table_Product[Product],Match(Menu!$F$2,Table_Product[Commodity Group],1),1)

    As far as I can tell it is the same as yours with different table names, worksheet names, etc. Please help me understand what I’m doing wrong. If it helps, the error message keeps highlighting this part: Table_Product[Product] the first time it occurs.

    Thanks Ray

    • Ray on June 30, 2016 at 1:13 pm

      I actually figured it out. There was a problem with the way I set up the table. Now it works like a charm, thank you.

  39. Colin on June 27, 2016 at 9:53 am

    How can I make this same functionality look to multiple ranges? Column A is “emulation” and allows for selection of “desktop”, “server”, “network”. Column B allows for different selections based on what is chosen in the corresponding cell in Column A. I’d like to be able to choose Column C based on what has been selected in B. To complicate things, Column C should allow for multiple selections from what is offered in the drop-down. Please help

    • Kurt LeBlanc on July 27, 2016 at 7:50 am

      Hey Colin,

      I’m not sure what approach you are using, but it sounds like the INDIRECT() approach would work best for your situation. It takes longer to set up, but it should be the solution you need:)

      Let me know if this helps!
      Kurt LeBlanc

  40. How To Create Drop Down In Excel Sheet | Straight Forward on July 11, 2016 at 12:01 pm

    […] Create Dependent Drop-down Lists with … – Thanks for the above example which has helped me a lot… I have applied this to amy excel. Actually i have to apply it to my full row and simultaneously the … […]

    • Kurt LeBlanc on July 12, 2016 at 5:27 am

      I’m glad it helped:)

      Kurt LeBlanc

  41. Cathy Harrington on July 18, 2016 at 7:02 pm

    HELP! I am following the one table approach. it works til the last step where the dd_reps has a INDEX/MATCH formula.
    Problem is my cell to MATCH is in another sheet.
    your example =INDEX(tbl_choices,,MATCH(region,dd_regions,0))
    I tried the following but not working—the sheet/cell represents my column header for rows with dd_regions
    my data =INDEX(tbl_LocationType,,MATCH(Inventory!$K$4,dd_Areas,0))

    any suggestions?

    thanks

  42. Cathy Harrington on July 21, 2016 at 11:44 am

    PLEASE HELP me fix this, I’ve worked for days and can’t make it work;
    I followed the one table approach. I have one primary and one secondary list grouped in one table.
    Primary works, secondary shows NOTHING.
    I’d really like the no blank option, but will be happy just to get the right data in the secondary
    I can send you may file;)

    Desperately,
    Cathy

  43. CL on August 2, 2016 at 4:38 am

    The #Headers to reference the table header row doesn’t work for me.

    • Kurt LeBlanc on August 2, 2016 at 6:11 am

      Hi CL,

      Can you please add some context, like your formula and the desired result, so that I can help you better?

      Thank you,
      Kurt LeBlanc

  44. Ri on August 2, 2016 at 11:43 pm

    Hi Kurt,

    Sorry for the late response and thanks for your reply. Actually, I figured out that there were lots of formatting on each name range. I removed the formatting and the file size reduced to minimal.

    Thanks alot and stay blessed.
    Riz

    • Kurt LeBlanc on August 3, 2016 at 6:46 am

      ok great! I’m glad you were able to figure it out.

      Kurt LeBlanc

  45. Kunal on August 12, 2016 at 11:38 pm

    I was wondering how can I block the second drop down after selecting certain items from the first drop down list?

    • Kurt LeBlanc on August 17, 2016 at 8:49 am

      Hey Kunal,

      Unfortunately Excel doesn’t have a function for locking a cell…You will have to set up a macro with an IF statement that checks the name and have it run every time you select a value:)

      Let me know if that helps you,
      Kurt LeBlanc

  46. Heidi-Jo Fonley on September 9, 2016 at 3:28 pm

    Thank-you so much! I have been looking for understandable instructions on how to do this for a week. Everytime I thought I had found the correct information, it didn’t work or the instructions would have gaps. You have no idea how happy I am looking at my fully functional dependent data validation drop downs.

    It does take time to set up all the names and be sure they are correct but it is time very well spent. The future maintenance, usage, and changes require a lot less time. So in the long, this is a major time savior.

    Again Thank-you,

    Heidi-Jo

    • jefflenning on September 9, 2016 at 4:54 pm

      Glad you got it working, and I’m happy to have helped 🙂

  47. Matthew Ingle on September 13, 2016 at 1:20 pm

    Just wanted you to know, this worked great! I used the ‘Two Tables’ option, but because of business constraints I couldn’t use tables and had to use column names. It worked just great for that as well.

    =INDEX(‘DROP DOWN MENUS’!$M$2:$M$126,MATCH(‘DATA INPUT SHEET’!V2,’DROP DOWN MENUS’!$N$2:$N$126,0),1):INDEX(‘DROP DOWN MENUS’!$M$2:$M$126,MATCH(‘DATA INPUT SHEET’!V19,’DROP DOWN MENUS’!$N$2:$N$126,1),1)

    Row M is the dependent column, N is the reference column. Anyway, it worked well with no tables as well.

    • jefflenning on September 13, 2016 at 1:41 pm

      Awesome…glad it worked and thanks for posting back with your example!

  48. Mo on September 27, 2016 at 11:41 am

    I am getting validation error in the 2nd list. The first list contains choices that are numeric, I changed a couple of them to Alpha characters and it is working fine. So, how can I rectify this error and still use the numbers.

    • Kurt LeBlanc on September 28, 2016 at 9:03 am

      Hey Mo

      hmmm… I think what’s happening are just differing number formats between the table names and what INDIRECT() returns. Try making sure the numbers be seen as text since that is what the table names should be.

      Let me know if that fixes it for you, and I’ll be glad to help you further:)
      Kurt LeBlanc

  49. Roneel Nandan on October 1, 2016 at 2:32 am

    Hi,
    I am trying to populate a data sheet where currently we have to filter from a large 700 line data set. For example we want to find a Stair Chair so from this 700 line table I have to manually filter by Equipt type and then it gives me the less options to chose the appropriate Equipt sub type. Instead of doing this manual for everytime, I want to have a drop down list when when I choose the Equipt type, then when I go to the next column it give the option to choose only the limited options of the subtype. Then As soon as this is chose I want the next colum to use the data in these 2 fields to auto populate a concatenated text. Please advise how I can do this as I am fairly new to excel and your help will be much appreciated

  50. Waseem on October 10, 2016 at 3:15 am

    Hello Jeff,

    Thanks for this wonderful guide.

    I am learning advanced excel by myself and came to this page while looking for advance data validation methods. I was able to learn the One Table approach only as of now.

    I was however wondering what was needed to be done to increase the number of drop downs; the above methods work in case of one primary and one secondary drop downs. What if I had a primary option and then had a secondary one and another one based on this secondary option?

    • Kurt LeBlanc on October 11, 2016 at 10:21 am

      Hey Waseem

      A tertiary drop-down would follow the same instructions as for the secondary drop-down but with the secondary drop-down as the reference:)

      Let me know if you have any trouble, and I’ll be glad to help,
      Kurt LeBlanc

  51. parsons on October 12, 2016 at 9:43 pm

    Hi Jeff,

    Thanks for the tips on how to use Index and Match.

    When I use the following in Data Validation
    =INDEX(tbl_secondary,MATCH(B7,Region,0),1):INDEX(tbl_secondary,MATCH(B7,Region,1),1)

    I encountered Excel error
    “You may not use unions, intersections, or array constants for Data Validation Criteria”

    I am using Excel for Mac 2011 Version 14.6.8

    Please advise. Thanks.

    Parsons

    • Kurt LeBlanc on October 14, 2016 at 6:49 am

      Hey Parsons

      I don’t have a Mac, so maybe someone else knows better…but it seems like you don’t need the range operator and second INDEX(). The first one is set to 0, for all rows, and the first one returns the first row. I would think the error would be that, because that would be like a circular reference. I’m sorry I can’t help you further, but I hope that helps you.

      Good luck!
      Kurt LeBlanc

  52. Claire on October 13, 2016 at 6:42 am

    I keep getting a formula error.

    I’m using the two table approach. My last name for my second drop down list formula is as follows:
    =INDEX(Table8[User],MATCH(‘Two Tables’!Sheet3!$B$2,Table8[Scenario],0),1):INDEX(Table8[User],MATCH(‘Two Tables’!Sheet3!$B$2,Table8[Scenario],1),1)

    I have three sheets in the excel spread sheet, hence why it pulls in sheet 3 before cell b2 (where the first drop down is). Any idea what’s going wrong?

    • Kurt LeBlanc on October 19, 2016 at 7:46 am

      Hey Claire

      I do see issue with the formula, concerning the sheet name ironically. It shouldn’t be pulling in two sheet names (‘Two Tables’!Sheet3!) The exclamation point in Excel formulas separates sheet names from a cell address within that sheet. So you may be typing in ‘Two Tables’! and clicking B2. That’s all I can think of because Excel will still pull in the sheet name if the formula is on a different sheet. Just click the cell:) That’s the only issue I see in your formula.

      Let me know if you’re still having an issue and need help,
      Kurt LeBlanc

  53. Anik Sachdeva on November 10, 2016 at 4:50 am

    Hi

    I downloaded “ConditionalDropdwon_2” excel file provided by you. In that workbook, I created a new sheet and copied contents of ‘input Sheet’ tab. Primary drop-downs obviously worked fine but Secondary drop downs does not work. Is there any way that it works across all the sheets?

    So precisely, I need these drop downs to work fine, every time i create a new sheet in a same workbook.

    • Kurt LeBlanc on November 10, 2016 at 9:12 am

      Hey Anik

      I was able to replicate the error and found an easy solution: if instead of copying the contents you copy the sheet (right-click the sheet tab > move or copy > check the box to create a copy), the drop-downs were working correctly for me.

      Let me know if that works out for you:)
      Kurt LeBlanc

  54. Anik Sachdeva on November 14, 2016 at 4:14 am

    Thanks a lot Kurt, it worked fine. All the defined names are replicated to another sheet (having scope = new sheet). But it saves an effort to write those defined names again on a new sheet.
    Thank you again!

    • Kurt LeBlanc on November 14, 2016 at 4:44 pm

      OK great!

      Kurt LeBlanc

  55. David on November 22, 2016 at 3:42 pm

    Hey,

    Would anyone be able to help, i have created a Drop down list that states my current projects on a spread sheet.

    How and what formula would i use so that when i click a certain job from the drop down list it fills in two other areas on the spread sheet such as the site address and the Project number? I have these on a separate tab.

    Any help would be much appreciated.

  56. M Armaghan on March 2, 2017 at 12:07 am

    Great Article Jeff.

  57. Dana on March 5, 2017 at 1:28 am

    Thank you very much for helping me figure out what am I doing wrong!

  58. Jim Patrick on March 21, 2017 at 10:48 am

    Thank you for putting this tutorial together. I have an issue with using dependent drop-downs. I can successfully create dependent drop-downs using the “indirect” coding in the data validation process. This is great as long as I do not change one of the drop-down choices that is a predecessor to other choices. If I change one of the initial choices there is no warning in the subsequent drop-down boxes that indicates that I need to make another selection. Is there a way to create a warning for this type of situation?
    Choice A: Dependent options 1 or 2
    Choice B: Dependent options 8 or 9

    If I make an entry for the initial choice and the dependent option I am fine. If I go back and change the initial choice from A to B, the dependent cell does not warn me that another selection is necessary.

    Thanks in advance — JP

    • Jeff Lenning on March 23, 2017 at 6:44 am

      Jim,
      We can accomplish that with conditional formatting. Essentially, we will ask Excel to format the cell with red fill, or red font, or any preference, when the value in the secondary drop down is not found in the corresponding secondary table. The steps for accomplishing this are:
      1 – select the secondary drop down cell
      2 – Conditional Formatting > New Rules > Use a formula to determine which cells to format
      3 – Use any formula that will return TRUE when the current secondary cell value isn’t in the corresponding secondary table, something like this would work: =NOT(COUNTIFS(INDIRECT(B7),C7))
      4 – select the desired format, perhaps a red fill

      I also have a blog post that talks more about using conditional formatting formulas here:
      https://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/

      Hope this helps!
      Thanks
      Jeff

  59. William T Roney III on April 11, 2017 at 2:26 pm

    I have a drop down list in A5 that one can choose different ages or eras. This drop down changes a dependent drop down in other cells (like B4 to I11) to only include certain items (actually only 1 item per age or era) from that age or era.
    If I choose a dependent drop down item from an age or era like “Automobile”, I want that item to change to the corresponding item from a different age or era automatically. For example, I have “Modern ERA” selected in A5, which has an item in a list called “Transportation”, associated with it in the dependent drop down of “Automobile”. I then select the ERA/AGE drop down and select “Pre-Modern ERA” and the dependent drop down should change to “Horse & Buggy”. “Horse & Buggy” is also in that list or “Transportation” items.

    How do I get my excel workbook to do that automatically? I don’t mind using macros either but cannot figure it out.

  60. ashiena on July 3, 2017 at 11:06 pm

    Such a good article and knowledge. Thank you

  61. Gabriel on August 7, 2017 at 8:45 am

    Hi thanks for your super post. I am hitting a problem with the argument =INDEX(tbl_choices,,MATCH(region,dd_regions,0))

    For some reason, Excel is saying there is a problem with the formula and it will not let me save it?!

    Any help would be greatly appreciated.

    Thanks again,

    Gabriel

    • Jeff Lenning on August 7, 2017 at 1:45 pm

      Gabriel, you may have to delete one of the two commas separating the first and second INDEX arguments. Hope it helps!
      Thanks
      Jeff

      • Gabriel on August 10, 2017 at 4:32 am

        Hi jeff,

        Thanks i figured it out in the end. The naming of the list didn’t work for some reason. Then all was good.

        Thanks

        • Jeff Lenning on August 10, 2017 at 4:33 am

          Great, glad you got it!
          Thanks
          Jeff

  62. Mark Gerlach on August 9, 2017 at 12:38 pm

    Jeff:

    Great article! Miss you in SoCal and particularly at Voyagers. Hope things are going well for you.

    I had to use this in a sheet where I had a series of values. I used the following to do lookups on the cell to the left:

    =INDEX(tblChoices,1,MATCH(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),dd_category,0)):INDEX(tblChoices,COUNTA(INDEX(tblChoices,,MATCH(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),dd_category,0))),MATCH(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1),dd_category,0))

    Probably an easier way to do this, but the single formula worked where the named ranges were throwing some errors.

    Take care,
    Mark Gerlach

    • Jeff Lenning on August 9, 2017 at 12:42 pm

      Hi Mark! Great to hear from you, hope all is well in sunny so cal 🙂

      That is one impressive formula my friend!
      Thanks
      Jeff

  63. Stefanie on October 3, 2017 at 10:28 am

    Hi, How can i set up this formula in a way of when I change the selection of the first drop down menu, it immediately set the second drop down menu to a valid option within that secondary drop down.
    As example, when I change the Region selection from “North” to “South”, the Rep field is still showing a rep from “North” list (as example JAB) . It is just until I click the secondary drop down menu when I can see the available options for South Region. This can cause confusion on users when filling out a Form, if they see the secondary filed already populated with a selection.
    So I’m looking for a way to set up this, so when I select “South” the rep field immediate change to one of the rep names of the region selected as example CJM).

    thanks for your help

    • Jeff Lenning on October 4, 2017 at 5:47 am

      Hi Stefanie!
      To my knowledge, there isn’t a built-in way to accomplish that. A macro could possibly help, but unfortunately, I don’t have any sample code to share. I will however add this to my “things to blog about” list because it could be pretty cool!
      Thanks,
      Jeff

  64. Mark C on November 1, 2017 at 8:49 am

    Hi Jeff, thank you so much for your post! I have a question about the ONE TABLE approach. Is there a way to make a 3rd drop down list that is dependent on the choice of Rep? (eg. a field in cell C8 that depends on the choice of rep, and there are different options for each rep). I understand that this is doable using the INDIRECT method, but I was wondering if there was a way to do this using the ONE TABLE approach.

    Thanks!

    • Jeff Lenning on November 1, 2017 at 8:52 am

      Hi Mark!
      I can’t visualize how to get a third set of choices into that single table, but, you could create another table to store the third drop-down choices and use the header row to store the secondary choices.
      Thanks
      Jeff

  65. Rakesh Kapoor on November 22, 2017 at 2:59 am

    Hi Jeff, thanks for the great post. I’ve a quick question, can we simply add the values to the columns instead of creating a drop down. I’ll explain with the example. Let’s take an example that I selected A in the A1 cell then values of Column B should be changed as One, Two, three and if I select B in A1 cell then values of column B should be changed to Ten, Eleven, Twelve, thirteen, Fourteen.

    I want these values instead of drop down. Please suggest the best way of doing the same.

  66. Dave on November 22, 2017 at 2:28 pm

    How would I adjust “region”, if I wanted a column of Regions with a column of Reps next to it?

    • Jeff Lenning on November 22, 2017 at 2:33 pm

      That is demonstrated in the “Multiple input rows example” Excel file (just scroll down till you get to the Additional Resources area.)
      Thanks
      Jeff

      • Dave on November 27, 2017 at 8:22 am

        Argh! Should have known you’d have thought of that! Many thanks!!

  67. Danny on December 16, 2017 at 11:27 pm

    hi Jeff,

    Why don’t we use the following in the data validation list:
    1) =INDIRECT(“tbl_Choices[#Headers]”) – for regiion
    2)=INDIRECT(“tbl_choices[“&G7&”]”) – Rep

    Thank you, DZ

    • Jeff Lenning on December 18, 2017 at 10:28 am

      Danny … you rock dude, that is awesome! I’ll update the post to include your alternative, which I love 🙂
      Thanks
      Jeff

  68. Tomas on January 17, 2018 at 7:17 am

    Hi Jeff,

    Thank you for your post. I have a question.
    How can I copy the same dependent drop down (INDIRECT) to other cells in column?
    I tried several options and the result is always the same. The first drop down is being copied but the dependent one in next cell is never. There is no choice available.

    Thank you.

  69. Phillip Zaphiropoulos on March 9, 2018 at 2:51 pm

    Hi Jeff,

    Thanks for your post! I’ve opted to use the two table method for my case, however I just cannot seem to get the dependent drop-down to show the proper list based on what I select in my primary cell. It works fine if I insert the dependent data validation list in a cell that’s not part of my main data table, but the moment I try adding the dependent data validation list to my main table that also houses the primary list, it will not retrieve the secondary options. Can I not have both the primary selection and the secondary selection in the same table? I have followed your Excel templates to a tee but still cannot figure out what I am doing wrong.

    Thanks so much.

    • Phillip Zaphiropoulos on March 9, 2018 at 2:55 pm

      I have figured out my problem. I realized my cell reference to the primary selection was absolute and not relative. Removed the ‘$’ and I was good to go!

      Cheers.

      • Jeff Lenning on March 9, 2018 at 3:07 pm

        Great … glad you got it 🙂
        Thanks
        Jeff

  70. Alex on March 21, 2018 at 10:28 am

    Don’t worry Jeff – nailed it!
    Thanks

    • Jeff Lenning on March 21, 2018 at 4:48 pm

      Excellent, thanks for letting me know and way to go 🙂

  71. waqas on May 12, 2018 at 3:03 am

    HI Jeff

    thanks for great workout, please clarify the below line since i am unable to allocate it as per my formula. I am not getting which fields this REGION represent in formula. the error pop ups that “the list source must be delimited list, or reference to single row or column”

    Assuming that the selected region value is stored in cell C5, which we’ve named region for convenience,

  72. Kate Blank on July 5, 2018 at 4:48 am

    Hi – Is there a conditional data validation tool for pivot tables?
    I have a dataset that includes a list of companies of three types (A, B, C). Each type of company has a different set of 3-7 fields that need to be completed for compliance.
    I would like build a pivot table that lets the user Filter for Company Type, with Rows being selected based on that filter.
    For example, Company Type A has Compliance Fields 1, 2, 3, 4, and 5, while Company Type B has Compliance Fields 6, 7, and 8.
    If the user filters for Company Type B, the pivot table should select Compliance Fields 6, 7, and 8 for the Rows, and the user should not see any other Compliance Fields.
    Maybe?
    Thank you!

  73. Shelli Godfrey on August 15, 2018 at 9:47 pm

    waqas, I had the same problem and found that I had not put the equal sign in the formula in the named range.

  74. Justin Stirling on November 12, 2018 at 9:22 am

    How can I set a data validation list such that if the drop down value in the cell is deleted, the cell will automatically show the a default value (i.e. “-Select-“)?

    For example, I have a worksheet with multiple drop down lists that I want to show a default value of “-Select-” once the sheet is opened. A user will select values from the lists that will impact the result of calculations throughout the entire spreadsheet. A user may accidentally “delete” the cell contents. If this happens, rather than the cell becoming blank, I want the cell to show a default value of “-Select-“. This scenario occurs in multiple random locations throughout the spreadsheet, not just in one localized place.

    I was able to come up with the following VBA code so far, but I’ve only figured out how to apply the concept over a range, rather than just cells that contain a drop down list. The problem with what I have coded so far is that every single blank cell in the range ends up with “-Select-” in it.

    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    ‘ If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range(“f2:p17”)) Is Nothing Then
    For Each cel In Range(“f2:p17”)
    Application.EnableEvents = False
    If IsEmpty(cel.Value) Then cel.Value = “-Select-”
    Next cel
    End If
    Application.EnableEvents = True
    End Sub

  75. Jill on November 13, 2018 at 10:19 pm

    Hi Jeff,

    Ultimately I am trying to create a multiple input rows data base that ignores blanks but I am struggling out of the gates. I have my named table with all choices (tbl_choices) and I have my primary data set, in my case, I have this labeled as dd_jobs. But for some reason my secondary drop-down is not returning any available options. The formula have I here is =INDEX(tbl_choices,,MATCH(‘Check Register’!A15,dd_jobs,0)). What am I missing!?

  76. Shubham on March 28, 2020 at 1:08 am

    Hi Jeff,

    Tips above helped alot in building matrices. But I am struck at a point Can you please help.

    I built mine header and dependent drop down and same is working fine for single cell, but when i am trying to extend same is giving error.

    Example:
    1. I have all header in column A and dependent in Column B, When selecting A1 i am getting corresponding drop down in B1
    2. When i got to cell A2 and select any drop down then in B2 corresponding drop down still shows referring to A1 where as it should show corresponding drop down from A2.

    Please help where did i went wrong. Not able to figure out.

  77. Anze on August 13, 2020 at 6:14 am

    Hi Jeff

    I am having problems setting up my dependant drop-down list because my first entry is made via an input form. So the neighboring column should contain a dependant drop-down list.
    Any suggestions?

  78. Conal on August 18, 2020 at 5:51 am

    Hi, Jeff

    I am trying to follow your formula (INDEX(table_choices…etc)

    I can’t fugure out how to configure the lookup value as you did with regions. Can you help me choose the correct lookup value as I have a Column in a table that contains Names that can be selected from a dropdown list within the sell.

    Hope you can help with this!

  79. Sarah on January 11, 2021 at 11:58 am

    Hi Jeff- I really hop you are still checking this page. I am using the two tables approach, but I am having issues with my secondary drop down. Using your Region and Rep table as an example, the secondary drop down options for region =”South” SHOULD include CJM, GML, PJ, and WSL. However, what I am actually seeing is CJM, GML, PL, WSL, CSR, LCW, MSS, SNM. So all REPs through the end of the list are included. Any idea what is causing this? I am stumped

  80. Jay on June 2, 2021 at 12:20 am

    Hi Jeff,

    I want to use this conditional data validation for multiple rows. I downloaded your working file for this and used the same formulas u have used in my sheet but i end up getting this error “The list source must be delimited list, or a reference to a single row or column”. I have tried multiple times to see where I have gone wrong and not able to fix this error. I replaced the data in your sheet with mine by adding a few columns and the validation works there but I used the same formula in my sheet and its not working. Could u pls help?

    Thanks,
    J

  81. Jason on November 24, 2021 at 5:13 am

    Hi Jeff

    I’ve implemented the one table, advanced version and it works really well for a single list of selectors. However, I want to implement this look up in a series of rows, so that the formulae relate to the preceding cells rather than a single selection box globally on a sheet.

    i.e. on a row by row entry of records, Column A would be the Region and Column B would be the Rep, and this can be entered on each row. Cell B1 would look up Cell A1’s value, Cell B2 would look up Cell A2’s value.

    Your formula currently uses a fixed single cell for selection. How could this be changed to do a relative lookup from the cell preceding, row by row?

    Thanks

    Jason

Leave a Comment