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!

** NOTE: 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. **

The solutions we will explore are:

  • One Table – hardest formulas, easiest ongoing data management
  • Two Tables – easier formulas, harder ongoing data management
  • INDIRECT – easiest formulas, hardest ongoing data management

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.

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 stores each secondary list in its own table. The formulas in this approach are by far the easiest to implement initially. This is a good option when there are not very many primary drop-down choices. Adding a new primary choice means setting up a new table to store the related secondary choices and special care must be made with naming conventions. This is not a good option if other users will need to frequently add primary drop-down options. Note that this approach has a limitation that the primary choices need to be valid table names, and therefore can’t contain spaces or funky characters.

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, plus one new table for each list of secondary options.

For example, we have a table to store all primary options, in our case the regions, North, South, East, West. Then, we set up a new table, named North, that stores related reps, in our case, DRR, FLR, JAB, MAE, MJH. Please note that the primary choice needs to be a valid table name, and thus, can’t contain spaces or funky characters. We set up another table named South to store those reps, and so one.

After each of the tables have been set up, and named with the name of the primary choices, it is time to set up the named reference.

We set up a new name, dd_primary, that refers to the name of the table that stores our primary drop-down choices.

Next, we navigate to the secondary input cell such as C7 (where we will soon set up data validation for the secondary drop-down) and then set up a new name dd_secondary that refers to the primary input cell such as B7 (the cell that will store the primary drop-down) as follows:

=INDIRECT(B7)

The INDIRECT function converts text string stored in the primary input cell, for example North, into a valid Excel reference that refers to the table named North. Please note that the table names must match the primary drop-down choices, for example, North and North. Also note that the the primary drop-down choices must conform to table naming conventions, and as such, can’t contains spaces or funky characters.

With the names set up, we select the primary input cell and turn on data validation to allow a list =dd_primary. We select the secondary input cell and turn on data validation to allow a list =dd_secondary. And, with that, we should be good to go!

This approach is demonstrated in the ConditionalDropDown3 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: ConditionalDropdown3
  • 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

 

This article was written by Jeff Lenning

111 comments:

  1. Chetan Karia
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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 !

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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

      1. Hameed
        Reply

        Thanks for this, quite useful and wonderful

  4. Roman
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

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

    1. jefflenning Post author
      Reply

      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

      1. Nick
        Reply

        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

        1. jefflenning Post author
          Reply

          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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    This was very useful, thanks!
    David

  17. Todd
    Reply

    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???

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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?

    1. jefflenning Post author
      Reply

      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
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

      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
    Reply

    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.

    1. jefflenning Post author
      Reply

      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. Виктор
    Reply

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

  26. Peter
    Reply

    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
    Reply

    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

    1. jefflenning Post author
      Reply

      Arth,
      There are a couple of ways to accomplish this task. If you have Excel 2013+ for Windows, then, one option to consider is using slicers instead of drop-downs. This idea is discussed in the following Excel University blog post:
      http://www.excel-university.com/slicers-as-an-alternative-to-conditional-drop-downs/

      If you want to use drop-down cells instead of slicers, then, I believe you can accomplish what you seek with the TWO TABLES approach discussed mid-way down the following post:
      http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/

      Hope this helps…and best of luck!
      Thanks
      Jeff

  28. Steve Brown
    Reply

    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!

    1. jefflenning Post author
      Reply

      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
    Reply

    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

    1. jefflenning Post author
      Reply

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

  30. Dragan
    Reply

    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.

    1. jefflenning Post author
      Reply

      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:
      http://www.excel-university.com/multi-column-lookup-with-vlookup-and-sumifs/
      http://www.excel-university.com/multiple-condition-summing-in-excel-with-sumifs/
      Hope these help…and best of luck!
      Thanks
      Jeff

  31. André SG
    Reply

    Thank you. Very helpful

  32. Mohammad
    Reply

    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

    1. Kurt LeBlanc
      Reply

      Hey Mohammad,

      Thanks for your additional information!

      Kurt LeBlanc

  33. Nilce Alves dos Santos
    Reply

    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.

    1. jefflenning Post author
      Reply

      Thanks, I’m happy to help 🙂

  34. Joeri
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      Can you send me the file directly? I can’t see a problem with it in terms of syntax.
      kurleb10@gmail.com

      1. Ray
        Reply

        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
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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. Pingback: How To Create Drop Down In Excel Sheet | Straight Forward

    1. Kurt LeBlanc
      Reply

      I’m glad it helped:)

      Kurt LeBlanc

  41. Pingback: How To Create A Drop Down Table In Excel 2007 | Crack A Tripcode

  42. Cathy Harrington
    Reply

    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

  43. Cathy Harrington
    Reply

    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

    1. Kurt LeBlanc
      Reply

      I’d be happy to help Cathy:) My email is kurleb10@gmail.com

      Kurt LeBlanc

  44. CL
    Reply

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

    1. Kurt LeBlanc
      Reply

      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

  45. Ri
    Reply

    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

    1. Kurt LeBlanc
      Reply

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

      Kurt LeBlanc

  46. Kunal
    Reply

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

    1. Kurt LeBlanc
      Reply

      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

  47. Heidi-Jo Fonley
    Reply

    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

    1. jefflenning Post author
      Reply

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

  48. Matthew Ingle
    Reply

    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.

    1. jefflenning Post author
      Reply

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

  49. Mo
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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

  50. Roneel Nandan
    Reply

    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

  51. Waseem
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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

  52. parsons
    Reply

    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

    1. Kurt LeBlanc
      Reply

      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

  53. Claire
    Reply

    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?

    1. Kurt LeBlanc
      Reply

      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

  54. Anik Sachdeva
    Reply

    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.

    1. Kurt LeBlanc
      Reply

      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

  55. Anik Sachdeva
    Reply

    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!

    1. Kurt LeBlanc
      Reply

      OK great!

      Kurt LeBlanc

  56. David
    Reply

    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.

  57. Jim Patrick
    Reply

    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

    1. Jeff Lenning Post author
      Reply

      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:
      http://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/

      Hope this helps!
      Thanks
      Jeff

Leave a Reply

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

By submitting this form, you accept the Mollom privacy policy.