fbpx
Search
Close this search box.

Dynamic (Cascading) Dropdowns that reset on change

Share

Facebook
Twitter
LinkedIn

Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall we?

Right, what’s on the (dropdown) menu?
Chandoo_CascadingDropdowns_NoChoices

Fruit, anyone?
Chandoo_CascadingDropdowns_First Choice

*BING!*
Chandoo_CascadingDropdowns_Not Done
Cool…check it out…as you can see from the above, the user gets prompted with “Choose…” whenever a subsequent choice must be made.

 

Ok, what kind of fruit should I have? Hmmm, let me see….eeny, meeny, miny, STRAWBERRIES!!!MO!
Chandoo_CascadingDropdowns_Strawberries

*BING!*
Chandoo_CascadingDropdowns_Still not finished

 

Ok, so what delights does Sub Category 2 have in store for me?
Chandoo_CascadingDropdowns_Earliglow

Earliglow? Never heard of it. Sounds delicious…I’ll have those, please.

*BING!*
Chandoo_CascadingDropdowns_Done

There, all done. Pretty nifty ehusers only get to see valid choices depending on what they chose last. So users simply can’t screw up!  Or can they?

[Evil user, determined to prove me wrong]: Wait a minute…I just remembered that mother expects me to eat my vegetables first, before I move on to dessert. So I better change that initial selection:

Chandoo_CascadingDropdowns_Change Initial Selection

*BING!*
Chandoo_CascadingDropdowns_Embarrassing
What the…Strawberries are vegetables???

Damn…changing upstream dropdowns later on means those downstream choices can be flat out wrong! So how can we make this bulletproof?

Macros to the rescue

Yep, we’ll use some code to clear out any ‘downstream’ choices if anything ‘upstream’ changes. Let’s go back to that original strawberry fest:
Chandoo_CascadingDropdowns_Done

Now watch what happens when our user subsequently decides they better vege out first:

*BING!*
Chandoo_CascadingDropdowns_Downstream Reset

Ahh…look at that: the code realized that all those downstream choices are no longer valid. So it deleted them, and prompted the user to choose again. There. Now that IS bulletproof.

So let’s see…hmmm…for an appetizer, I’ll have baby carrots:
Chandoo_CascadingDropdowns_Baby Carrots

 

And I already decided on Strawberries for pudding…
Chandoo_CascadingDropdowns_Pudding

 

But what about my main course. Ah, yes, of course…
Chandoo_CascadingDropdowns_Human

MEAT! Yummy. BURP!

What’s the recipe?

My approach draws on Roger Govier’s excellent sample file on the Contextures website. Be sure to check out that link to see Roger’s in-depth discussion of the formula magic behind this puppy…It’s genius.

In my Dynamic-Dependent-dropdowns-20140214, you’ll see that all the different categories used by the dropdowns are hosted in an Excel Table, that has the initial categories down the left hand side, and subsequent categories across the top:
Chandoo_CascadingDropdowns_val list

So how do these categories get used by the data validation dropdowns? Roger’s approach uses two dynamic named ranges to feed the data validation lists, one called MainList and one called SubList:
Chandoo_CascadingDropdowns_Name Manager

Here’s the MainList formula:
=INDEX(Table1[[Choose…]],1):INDEX(Table1[[Choose…]],COUNTA(Table1[[Choose…]]))

…and here’s the SubList formula:
=IF(OR(Sheet1!B8="Choose…",Sheet1!B8=""),"",INDEX(Table1,1,MATCH(Sheet1!B8,Table1[#Headers],0)):INDEX(
Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!B8,Table1[#Headers],0))),MATCH(Sheet1!B8,Table1[#Headers],0)))

The SubList formula has a relative reference in it: whatever cell you use it in, it retrieves the value of the cell to the immediate left, and then it scans the column headers of our validations table (Table1) looking for the heading that matches that value. Once it’s found it, it simply uses the items listed underneath that heading.

Because this formula is relative, before you enter it into the Name Manager, you will need to first select cell C8, because the above relative formula refers to B8 – the cell to the left. (Note that it doesn’t matter what is in C8 or where your actual dropdown are…rather it’s just that the above formula happens to refer to B8, and because we want our formula to always reference the cell on the immediate left, then we’ve got to select the cell to the immediate right before we enter this relative formula into the Name Manager.

Also note that my version of Roger’s approach uses Excel Tables and the associated Structured References that Table functionality allows. My table is called Table1. Your validation lists MUST be held within an Excel Table (which requires Excel 2007 or greater) and you MUST change the Table1 references in the above formula to match the name of your table.

Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and are a very powerful and simple way to store things like lists, chart data, and PivotTable data…especially if you might need to add more data to your spreadsheet at a later date, and want to avoid having to repoint all your formulas to include the additional data. If you’re not familiar with Excel Tables – or you don’t know what that Table1[#Headers] guff above means – then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.

The way these two formulas work is very clever. That MainList named range only gets used by dropdowns in that very first ‘Main Category’ column:
Chandoo_CascadingDropdowns_MainList2

…and all other ‘downstream’ dropdowns – no matter what level they are – are fed by the SubList named range:
Chandoo_CascadingDropdowns_SubList2

The beauty of Roger’s approach is that it can handle any number of cascading levels, provided all the category names are unique. All you need to do is simply add the new subcategories to the right hand side of our validations table (Table1).

Let’s look at an example. If you look at the below screenshot, you’ll see that users can choose from a number of different kinds of meat:
Chandoo_CascadingDropdowns_Meat List

Let’s add a further level that would give meat eaters some further choices relating to how their meat is prepared.

To set this up, all we need to do is take the individual items from that ‘Meat’ column and add each one as a new column header:
Chandoo_CascadingDropdowns_New Headers

Then we simply list the new options for each type of meat below the relevant header:
Chandoo_CascadingDropdowns_Flesh out

Now here’s the magic: as soon as we add another column to our input table and set it up with data validation – which I did simply by clicking on the bottom right corner of the cell with the word ‘Human’ and dragging it across – then Excel picks up on the fact that there’s a sub-subcategory, and serves it up to us. *BING!* Order up!
Chandoo_CascadingDropdowns_Raw Person

Add code, and stir-fry for 10 milliseconds

As mentioned earlier, in addition to Roger’s great method, I’ve written some code that clears out any downstream entries in the event that an upstream entry is changed. It’s in the sample workbook already, all set to go. But here’s the actual code, for you VBA nerds. (Special thanks to Gabor Madacs for some enhancement suggestions)

Option Explicit

Const CHOOSE = "Choose…"

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrorHandler
    Dim targetCell As Range
    Dim nextCell As Range
    Dim oldCalc As Excel.XlCalculation
    
    If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
        If [Radio_Choice] = 1 Then
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
                oldCalc = .Calculation
                .Calculation = xlCalculationManual
            End With
            
            For Each targetCell In Target
                'Clear any cells that use 'SubList' to the right of targetCell in the current table.
                If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
                    For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
                        If HasValidationFormula(nextCell) Then
                            If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
                        End If
                    Next nextCell
                End If
                
                'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
                ' or a 'SubList' dropdown
                If HasValidationFormula(targetCell) Then
                    Select Case targetCell.Validation.Formula1
                    Case "=MainList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing.
                        Else
                            targetCell.Offset(, 1).Value = CHOOSE
                        End If
                        
                    Case "=SubList"
                        If targetCell.Value = "" Then
                            targetCell.Value = CHOOSE
                        ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
                            targetCell.Value = ""
                        ElseIf targetCell.Value = CHOOSE Then
                            'Do nothing
                        Else
                            Set nextCell = targetCell.Offset(, 1)
                            If HasValidationFormula(nextCell) Then
                                If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
                            End If
                        End If
                    End Select
                End If
            Next targetCell
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
                .Calculation = oldCalc
            End With
        End If
    End If
    Exit Sub
ErrorHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        If oldCalc <> 0 Then .Calculation = oldCalc
    End With
    MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
End Sub

Private Function HasValidationFormula(cell As Range) As Boolean
    On Error GoTo ValidationNotExistsError
    If cell.Validation.Formula1 <> "" Then
        HasValidationFormula = True
    Else
        HasValidationFormula = False
    End If
    Exit Function
ValidationNotExistsError:
    HasValidationFormula = False
End Function
 


Hungry for more?


Here’s some related Posts at Chandoo.org:

Download the file

To see how this is done, download this file and enable macros:
Dynamic-Dependent-dropdowns-20140214

About the Author.

Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:

=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))

That’s right, pure #VALUE!

Find out more at http:www.heavydutydecisions.co.nz

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

108 Responses to “Dynamic (Cascading) Dropdowns that reset on change”

  1. one idea about to avoid changing the category after selecting the first choice
    we can use something like this:
    MainList =IF(ISBLANK(OFFSET(INDIRECT("rc",),,1)),OFFSET(Table1[[Choose… ]],,,COUNTA(Table1[[Choose… ]])),"")

    here:
    http://www.myonlinetraininghub.com/excel-factor-19-dynamic-dependent-data-validation
    find a different approach to the same problem of validation. It is used only one name, the table does not have a first column with the categories (your Choose column), there is no need to update it when we add new columns

    • Jeff Weir says:

      Hi Roberto. I steered clear of INDIRECT on account of it's volatility. I never use it when there's an alternative. Same goes for OFFSET etc. While on small datasets you won't notice any issues, I can never guarantee that a user won't implement something on a very big model with tens of thousands of downstream formulas. So I never use it.

      That said, your approach highlights that I could do something similar with INDEX and still just have the one formula. Thanks for that...I'll whip something up when I get a moment.

      • Jeff Weir says:

        Also note that you don't need to update my first column any more than in your approach, unless I'm mistaken.

      • I use indirect and offset when I need them ... I avoid the use when it is necessary
        advice to those who are interested to start from here:
        http://www.decisionmodels.com/calcsecretsi.htm

        • Jeff Weir says:

          I take a more extreme approach...I avoid INDIRECT and OFFSET like the plague unless I need them. I can't recall the last time I had to fall back on INDIRECT, and often the only time I need to use OFFSET is in formula challenges.

          • @Jeff said:
            I take a more extreme approach…I avoid INDIRECT and OFFSET like the plague unless I need them.

            this is the attitude of those who are afraid of something ... I can assure you that those functions do not bite 😀

          • Jeff Weir says:

            Those functions are like the family pet dog. Everybody loves it until one day it rips a chunk out of someone's face. 😉

            I recently stripped out all of the INDIRECTS and OFFSETS out of a model someone else built that had a recalc time of approx 2 minutes - even if you went to a new sheet and typed the word 'waiting' in.

            Now it recalcs in milliseconds in most cases.

            It didn't even have many INDIRECTS and OFFSETS it it. Rather, it had a very long chain of downstream formulas that pointed at ..wait for it...some dynamic dropdowns that used INDIRECT.

          • Jeff said:
            Those functions are like the family pet dog. Everybody loves it until one day it rips a chunk out of someone’s face. 😉

            OMG now I have a little of anxiety ... as soon as I get home I will give a pat and a biscuit to my dog ??and give him lots of cuddles 🙂

            However seriously, in this scenario (and even with thousands of formulas) where the formula is used in a defined name and then called from validation, do you think that this situation may create delays recalculation?

            other questions:
            1) I guess if you avoid offset and Indirect functions like the plague ... you will avoid the conditional formatting like the nerve gases, is not it?
            (I ask this because I am with the idea of ??Murtaza)

            2) I did not take a long time for your code but this line
            [...]
            For Each targetCell In Target
            [...]
            I think it might be better so:
            [...]
            set rng=Intersect(Target, [DataEntryTable])
            For Each targetCell In rng
            [...]
            I got an error when I accidentally copied column A and I pasted in column B
            thanks
            r

          • Jeff said:
            Also, to better illustrate the problem of using just one validation list, see the below file ...

            Using a single formula does not prevent you to run multiple checks. I had not added because the aim was a different one.
            I have already adapted the file to run all the controls and it was very simple ... please look at the last file that I have attached.

      • @Jeff said:
        That said, your approach highlights that I could do something similar with INDEX and still just have the one formula.

        Just add the IF condition.
        the concept of IF condition:
        =IF(ISBLANK(OFFSET(INDIRECT(“rc”,),,1)), ...
        is this:
        if you are in cell B1 can be rewritten as:
        =if(isblank(c1, ...
        i often use INDIRECT(“rc”,) because so I'm sure that the user does not miss.
        Of course many tasks in an event procedure is much more dangerous than the volatility of the function INDIRECT

        • Jeff Weir says:

          However seriously, in this scenario (and even with thousands of formulas) where the formula is used in a defined name and then called from validation, do you think that this situation may create delays recalculation?

          Yes it can. And it did, as I said happened in my comment above. I'll whip up an example and post it sometime soon as a new blog topic. To be sure, it was in a poorly designed model that had way to many dependency cells. But all the models that come to me for fixing are poorly designed, and often my brief is to do the bare minimum I can to get them working faster. Stripping out Volatile functions is one of the first things I do. Often that alone is enough to enable calculation to be set to Automatic again. Calculation should NEVER be set to manual if you can't avoid it. Usually you can.

          I guess if you avoid offset and Indirect functions like the plague … you will avoid the conditional formatting like the nerve gases, is not it
          This is true, I often use VBA to format large ranges instead of conditional formatting. In a recent Project Costing Model I built, I found that CF was really really slow things down when copying sheets. CF added many seconds delay to the process. When I took CF out, it worked in milliseconds.

          And often for some bizarre reason I find that CF rules duplicate themselves, so that I end up with ten or twenty rules when previously I had only a handful - particularly if I allow users to cut and paste ranges in an input sheet.

          Plus, given I'm building applications that others must maintain after I leave, I often prefer to have the 'logic' written in code, rather than buried in CF formulas.

          • @Jeff
            I think that what you've described are exceptions. In those cases, I am with you, I never said otherwise. But the norm for me is a well designed file.
            The formulas used in names are calculated only when we use a reference to the name. In this specific case will be calculated only when you change a cell's validation. How many times can this happen? I think in this case the number of recalculations is irrelevant.
            In addition, if a well-designed file is unlikely to have problems with recalculation time. If I have a file with thousands of formulas of course I also avoid the use of FC and volatile functions.

          • Jeff Weir says:

            I just realized that your example file is only set up to handle two levels. So where you say in your first comment:
            ...a different approach to the same problem of validation. It is used only one name, the table does not have a first column with the categories (your Choose column), there is no need to update it when we add new columns

            ..this will only work for 2 levels of choice. As soon as you add a 3rd level of choice, then your approach falls down, because users get presented with a dropdown that includes all subcategories.
            Incorrect

            Whereas Roger Govier's approach that I'm using easily handles any number of sub-levels, as per my introductory paragraph.

            Also, to better illustrate the problem of using just one validation list, see the below file:
            http://chandoo.org/wp/wp-content/uploads/2014/02/Dynamic-Dependent-dropdowns_One-Formula.xlsm

          • Jeff said:
            ..this will only work for 2 levels of choice. As soon as you add a 3rd level of choice, then your approach falls down, because users get presented with a dropdown that includes all subcategories.

            UFFF, No Jeff!
            are only 2 levels because the table is designed for 2 levels ...
            es. you add this column:
            Apples
            Braeburn
            Fuji
            Jazz
            Gala
            Golden Delicious
            Granny Smith

            and you will have the third level

            Add other column:
            Fuji
            Red
            Green
            Yellow

            and you will have the fourth level

        • Jeff Weir says:

          Roberto: I think that IF(ISBLANK(... approach will let users choose something incorrect in the event that say they have made a choice in the first dropdown, and are trying to choose something from the 4th dropdown without first filling in the 2nd or 3rd.

          Incorrect

          • @Jeff used you this formula?
            MainList=IF(OFFSET(ISBLANK(INDIRECT("RC",0),,1)),INDEX(Table1[[Choose… ]],1):INDEX(Table1[[Choose… ]],COUNTA(Table1[[Choose… ]])),"")

            This prevents only change Main category column if we have already selected sub ??category 1

          • Jeff Weir says:

            I used this:
            =IF(ISBLANK(Sheet1!D8),INDEX(Table1[[Choose… ]],1):INDEX( Table1[[Choose… ]],COUNTA(Table1[[Choose… ]])),IF(OR( Sheet1!D8="Choose…",Sheet1!D8=""),"",INDEX(Table1,1,MATCH( Sheet1!D8,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX( Table1,,MATCH(Sheet1!D8,Table1[#Headers],0))),MATCH( Sheet1!D8,Table1[#Headers],0))))

            I'm away for next few days, so will touch base when I get back.

          • @Jeff said:
            I used this:
            =IF(ISBLANK(Sheet1!D8),INDEX(Table1[[Choose… ]],1):INDEX( Table1[[Choose… ]],COUNTA(Table1[[Choose… ]])),IF(OR( Sheet1!D8=”Choose…”,Sheet1!D8=”"),”",INDEX(Table1,1,MATCH( Sheet1!D8,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX( Table1,,MATCH(Sheet1!D8,Table1[#Headers],0))),MATCH( Sheet1!D8,Table1[#Headers],0))))

            You do not say which one is the active cell when you use that formula ... I think it must be the cell C8 but I infer this from the second part of the formula ... so the first part is certainly wrong

            since you are using 2 names you will need to change both formulas ... well ... with B8 as activecell these are the names you need to use:
            MainList=IF(ISBLANK(Sheet1!C8),INDEX(Table1[[Choose… ]],1):INDEX(Table1[[Choose… ]],COUNTA(Table1[[Choose… ]])))

            SubList=IF(OR(Sheet1!A8="Choose…",Sheet1!A8="",NOT(ISBLANK(Sheet1!C8))),"",INDEX(Table1,1,MATCH(Sheet1!A8,Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!A8,Table1[#Headers],0))),MATCH(Sheet1!A8,Table1[#Headers],0)))

            This is the customization for your solution. I think it will work properly.
            Another question if you want to answer ... in the event procedure you set the cell values ??= "" ... why it does not erase the value instead? In the formulas, you can test only the empty value instead of having to test "" and empty value
            regards
            r

          • Jeff Weir says:

            Roberto:
            You do not say which one is the active cell when you use that formula … I think it must be the cell C8 but I infer this from the second part of the formula … so the first part is certainly wrong

            Cell E8 was the active cell. So I don't believe this is wrong.

            since you are using 2 names you will need to change both formulas

            I am only using one name to trial this approach, not two. So all dropdowns have the same validation source.

            I think you're misinterpreting what I am trying to say. So I'll use your file from http://www.myonlinetraininghub.com/excel-factor-19-dynamic-dependent-data-validation by way of example.

            If you have more than two dropdowns, and if your validation formula returns the 'main' list in the case that the cell to the left is blank, then a user can do this:
            example

            ...which obviously is incorrect...they should not be able to choose anything from the Choice 3 box if they have not yet chosen from the Choice 2 box.

            Now, most users obviously won't be stupid enough to choose something from box 3 without first choosing something from box two. But it is a possibility. Having 2 validation formulas rules that possibility out, with no downside other than the fact that you have 2 formulas, not one.

            ---Edit---
            In fact, you don't need more than two dropdowns...users can stuff things up even if there are only two dropdowns:
            Roberto 2

          • I'm sorry, I confess that I am a bit confused ... I never talked about my solution but always about your solution and your file.

            Now you said that you spoke of my solution ... well this is the link to my file changed to solve this problem:
            http://goo.gl/8rTBqp
            I hope there are no more misunderstandings.

            regards
            r

          • In this second version you can not change your selection after that the next choice was made:
            http://goo.gl/p5yHkA

            regards
            r

      • Kenny says:

        Hi Jeff,
        Thanks very much for your brilliant Post. It is awesome.
        Could you please, advise me - my lists are not arranged in the way you have arranged yours in the example above (i.e. sideways). I want to arrange my lists differently: I have my main category of products (i.e. the 'Categories' column shown on the far right of the example below as (MainList), and the dependent lists (i.e.SubLists) including their prices as shown in columns A, B & C (left side of the example below).

        Category Description Price Categories Choose… Choose… Choose…
        Coats Green size 99 £1.00 Coats
        Coats Purple size 99 £2.00 Hats
        Hats Med Wide tail £7.00 Shoes
        Hats Large Wide tail £8.00 Suits
        Shoes Size 12 Brown £13.00 Ties
        Shoes Size 15 Yellow £14.00
        Suits Blue size 56 £19.00
        Suits Yellow size 59 £20.00
        Ties Swirl Deluxe £25.00
        Ties Wrap and stay £26.00

        When I applied the following INDEX formula to the 'MainList', it worked OK:
        =INDEX(Table2[Categories],1):INDEX(Table2[Categories],COUNTA(Table2[Categories])).

        However, I couldn't get the formula which controls the dependent cells (i.e. the 'SubList' formula to work for me. What I came up, by adjusting your own original formula is as follows:

        =IF(OR(Sheet1!G24="Choose…",Sheet1!G24=""),"",INDEX(Table3[Category],1,MATCH(Sheet1!G24,Table3[#Headers],0)):INDEX(Table3[Category],COUNTA(INDEX(Table3[Category],,MATCH(Sheet1!G24,Table3[#Headers],0))),MATCH(Sheet1!G24,Table3[#Headers],0)))

        With the above formula in place, when I click on the dependent cell, data validation, and type in =SubList as the list source, I get an error message as follows:

        "The list source must be a delimited list or a reference to single row or column". With this message, the '=SubList' typed it is rejected.

        It is clear I am doing something wrong. I am not very knowledgeable in the use of INDEX and MATCH formula. I really like your approach to dependent data validation - although I prefer the lists from top to bottom and not left to right.

        I should be very grateful if you would, please, help advise me on how I can resolve the problem that I am having getting the Data Validation List Source box (for my dependent cell) to accept the name '=SubList'.

        Thank you.

        Kenny

        • Jeff Weir says:

          Hi Kenny. Probably better you post your question at Chandoo.org/forum where you can also upload a sample file.

          • Kenny says:

            Dear Jeff,

            Thanks heaps for your prompt response to my Post. That was indeed very kind of you. I appreciate that.

            As you advised, I have posted a question at Chandoo.org/forum and I also attached a Sample file. Here is the link:

            http://www.chandoo.org/forum/threads/dependent-data-validation-with-index-match.19350/

            As I indicated to you in my initial post, I found your approach to this subject very brilliant. I have read a few other articles/examples on this matter. Some of them, although very good, have not really touched on the areas that I find confused. But your Post did just that and many more.

            I should be very grateful if you will, please, find time to read my question at Chandoo (see the link above), and kindly help me. I have no doubt that there will be others who may, in the kindness of their hearts, wish to proffer solution. But since my desire is based on your article, I have no doubt that you will be in the best position - with your knowledge, skills and experience, to sort it out for me.

            In the sample file that I attached, I took out a few sheets/information, and left only those sheets where I have problems with. My probolems reside in the PRODUCT sheet containing the listing of the products and the INVOICE sheet where the data validation drop-downs are housed Columns G and H.

            Again, thanks for all your help and support. I remain eternally grateful to you.

            Kenny

  2. Oz du Soleil says:

    FASCINATING! Thanks for this solution. I've also dealt with this and posted a video just 2 days ago.

    http://youtu.be/Z77dXPoklYs

    • Jeff Weir says:

      Nice one, Oz. As per the comment above I steered clear of INDIRECT because of it's volatility, as I can never guarantee that a user won’t implement something on a very big model with tens of thousands of downstream formulas.

      • Oz du Soleil says:

        Jeff, excellent point.

        My work is typically a small amount of data but it's someone's tiresome task that they'd like to automate. So, volatility and resource hogs (VLOOKUP) are rarely an issue.

        But I've seen where spreadsheets drag and even crash because of a huge amount of certain functions.

  3. S.Prasad says:

    Here I should make a mention of BI tool, Qlikview. Though Excel can do wonders but Qlikview could have solved the above problem easily as it works on the associative selection.

    • Oz says:

      Qlikview is free for just one person. The pricing quickly jumps to over $1000. Good luck if you can't afford it or, you've gotta convince a manager to approve paying for it. In the meantime, there's Excel.

      Also, these types of solutions tend not to be in isolation. There are other things going on in a spreadsheet that supports or relies on this piece. So, there's probably more needed than just a solution to the cascading dropdown lists. Then, the question becomes: can Qlikview support EVERYTHING that's needed? Do we want to go down that rabbit hole when both Jeff and I show 2 decent Excel solutions?

  4. Ram says:

    Human Meat. Sounds Tasty

  5. Gabor Madacs says:

    Hi Jeff,
    I have fixed a bug and some general problems in the VBA code: 🙂
    - It had always switched the Calculation to Automatic
    - There was no error handling - it is a must, if we disable EnableEvents or ScreenUpdating
    - There was a very resource consuming check for if there is a validation or not
    (It's got a new function.)
    - I've extracted the "Choose…" string to a constant.
    (This is rather a fragile one, due to the "…" (ellipsis) character.)

    (Um, this box is really small... :))
    ------------------------------------------------------------

    Option Explicit
    
    Const CHOOSE = "Choose…"
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo ErrorHandler
        Dim targetCell As Range
        Dim nextCell As Range
        Dim oldCalc As Excel.XlCalculation
        
        If Not Intersect(Target, [DataEntryTable]) Is Nothing Then
            If [Radio_Choice] = 1 Then
                With Application
                    .EnableEvents = False
                    .ScreenUpdating = False
                    oldCalc = .Calculation
                    .Calculation = xlCalculationManual
                End With
                
                For Each targetCell In Target
                    'Clear any cells that use 'SubList' to the right of targetCell in the current table.
                    If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
                        For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
                            If HasValidationFormula(nextCell) Then
                                If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
                            End If
                        Next nextCell
                    End If
                    
                    'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
                    ' or a 'SubList' dropdown
                    If HasValidationFormula(targetCell) Then
                        Select Case targetCell.Validation.Formula1
                        Case "=MainList"
                            If targetCell.Value = "" Then
                                targetCell.Value = CHOOSE
                            ElseIf targetCell.Value = CHOOSE Then
                                'Do nothing.
                            Else
                                targetCell.Offset(, 1).Value = CHOOSE
                            End If
                            
                        Case "=SubList"
                            If targetCell.Value = "" Then
                                targetCell.Value = CHOOSE
                            ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
                                targetCell.Value = ""
                            ElseIf targetCell.Value = CHOOSE Then
                                'Do nothing
                            Else
                                Set nextCell = targetCell.Offset(, 1)
                                If HasValidationFormula(nextCell) Then
                                    If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
                                End If
                            End If
                        End Select
                    End If
                Next targetCell
                With Application
                    .EnableEvents = True
                    .ScreenUpdating = True
                    .Calculation = oldCalc
                End With
            End If
        End If
        Exit Sub
    ErrorHandler:
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
            If oldCalc <> 0 Then .Calculation = oldCalc
        End With
        MsgBox Err.Description, vbCritical, Name & ".Worksheet_Change()"
    End Sub
    
    Private Function HasValidationFormula(cell As Range) As Boolean
        On Error GoTo ValidationNotExistsError
        If cell.Validation.Formula1 <> "" Then
            HasValidationFormula = True
        Else
            HasValidationFormula = False
        End If
        Exit Function
    ValidationNotExistsError:
        HasValidationFormula = False
    End Function
    


    ------------------------------------------------------------

    I hope it helps. :)

    Best wishes,
    Gabor Madacs
    of The FrankensTeam
    https://sites.google.com/site/e90e50fx

    • Jeff Weir says:

      Hi Gabor. Fair point re the calculation - I should first check whether it is off. That said, in my opinion, if a user has switched calculation off then they have a spreadsheet design problem that they should address, as I outlined recently in http://chandoo.org/wp/2014/01/17/big-trouble-in-little-spreadsheet/

      And fair point re error handling too, although I usually don't bother putting it in in my trivial code, because I'd notice if something went wrong. But this code is for public consumption, so you're quite right that I've been slack!

      And your validation check is more elegant...I simply never thought of the On Error Resume Next approach instead of the If Not rngTest Is Nothing Then approach I took. That said, I wouldn't say that my approach was "very resource consuming" ...it only gets performed one, and it's over before the user lifts their hands off the mouse. Must consume nanoseconds extra compared to yours.

      But yours is more elegant, to be sure. I'll update the code above accordingly.
      Cheers

      Jeff

      • Jeff Weir says:

        The code could still do with a bit more tweaking...I need to put something in to handle the case where a user selects and deletes all the dropdowns in one go. Currently this populates each dropdown and sub dropdown with "Choose...".

        Will be an easy fix, but I won't bother making it until I've changed the file to just the one validation formula, as per Roberto's approach.

      • Gabor Madacs says:

        Hi Jeff,
        The my problem is not with the "If Not rngTest Is Nothing Then" part but this:
        Set rngTest = Intersect(cell, UsedRange.SpecialCells(xlCellTypeAllValidation))

        Frist, the UsedRange.SpecialCells(xlCellTypeAllValidation) function was called multiple times, within two nested for loop. This had calculated and returned the same value for every call. (It may be cached, maybe not in the Excel sheet.)

        Next, you (try to) create a new Range object in the memory for every check by the Intersect() function. Then this object should be garbage collected after the check.

        This is what I call "very resource consuming" - compared to an Error check function. 🙂

        You are right, all of this will run only if we are in the DataEntryTable area.
        But I think we should always try to build a better performing code, then we will face less troube next time, when we want to reuse our trusty solution on a bigger scale...
        🙂

        Best wishes,
        Gabor Madacs
        of The FrankensTeam
        https://sites.google.com/site/e90e50fx

  6. Gabor Madacs says:

    Errr... The forum engine stole the indents. 🙁

    I have uploaded the fixed Excel file to our site:
    http://goo.gl/cTej6b

    Gabor
    (Not really a VBA nerd, just a professional developer. ;))

  7. Murtaza says:

    I suggest to highlights invalid inputs by using conditional formatting for mainly 2 reason: Avoid VBA, Avoid re-typing every input.

    Avoid re-typing every input:
    for example if you need to filll 10 dependent columns some may still be valid even if you change the parent column. In this case, the highlighting only invalid cells a little comfort.

    Implementation:
    1. Add Named range "Check" with "=IF([ThisCellValue]"",ISNA(MATCH([ThisCellValue],Uselist,0)))
    *[ThisCellValue] = reference to current cell with sheet name.

    2. Set Conditional Format "=Check" if it is true turn it RED

    • Jeff Weir says:

      What's the reason for avoiding VBA?
      How likely is it that if you change a dropdown that the dropdowns downstream will still be correct? Depends on the data, but in almost all cases I've actually employed cascading dropdowns, the sublists are almost unique. So you have to change every downstream one if you change an upstream one.

      • @Jeff said:
        So you have to change every downstream one if you change an upstream one.

        you're right, but this must be done in every case. The CF may indicate that the value of a cell is incorrect. So when the category is changed, the user realizes that he has to change other cells or clear them ... all in all I think this is a good alternative

        • Jeff Weir says:

          Sure, CF is a good alternative to VBA. But Murtaza was saying that the reason to use CF is to avoid VBA.

          There certainly are some reason to avoid VBA in some circumstances, but that doesn't mean it should be avoided at all cost.

          Mind you, you could argue the same about volatile formulas 😉

  8. Jeff Weir says:

    Roberto: The confusion above is because on the site you mention above http://www.myonlinetraininghub.com/excel-factor-19-dynamic-dependent-data-validation there are multiple links going to different versions of your file.

    I had downloaded the file available from the first link "Download the Excel workbook." which I see is very different from the other file available from the "Download the workbook" file.

    Sorry for the misunderstanding.

  9. Jeff Weir says:

    Roberto: We may cross post here, so apologies if you're in the process of replying.

    The file dynamic_validation_table_for_jeff_2 shows all subcategories from the main dropdown:

    Roberto4

    ...which to me means the approach isn't suitable for multiple levels.

  10. Jeff Weir says:

    Okay, I see we can amend the approach by using this formula (entered while B8 is the active cell):

    =IF(NOT(ISBLANK(Sheet1!C8)),"",IF(Sheet1!$B$7=Sheet1!B$7, INDEX(Table1[[Choose… ]],1):INDEX( Table1[[Choose… ]], COUNTA(Table1[[Choose… ]])),IF(OR( Sheet1!A8="Choose…", Sheet1!A8=""),"",INDEX(Table1,1, MATCH( Sheet1!A8, Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX( Table1,,MATCH (Sheet1!A8,Table1[#Headers],0))),MATCH( Sheet1!A8, Table1[#Headers],0)))))

    ...but that's a real mouthful.

    I do like the way it works, though...nice approach Roberto. No VBA necessary...just force the user to clear 'downstream' dropdowns. Will put an update in the main article to talk this approach through.

    • Jeff said:
      …which to me means the approach isn’t suitable for multiple levels.

      Reply:
      now I understand what you mean and you're right

      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

      Jeff said:
      Okay, I see we can amend the approach by using this formula (entered while B8 is the active cell):

      =IF(NOT(ISBLANK(Sheet1!C8)),”",IF(Sheet1!$B$7=Sheet1!B$7, INDEX(Table1[[Choose… ]],1):INDEX( Table1[[Choose… ]], COUNTA(Table1[[Choose… ]])),IF(OR( Sheet1!A8=”Choose…”, Sheet1!A8=”"),”",INDEX(Table1,1, MATCH( Sheet1!A8, Table1[#Headers],0)):INDEX(Table1,COUNTA(INDEX( Table1,,MATCH (Sheet1!A8,Table1[#Headers],0))),MATCH( Sheet1!A8, Table1[#Headers],0)))))

      …but that’s a real mouthful.

      xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
      it seems to me a good compromise, if we delete row 1 in table1 (where there are "Choose…" strings) we can use this formula (more readable):
      (entered while B8 is the active cell)
      =IF(ISBLANK(!C8),IF(!$B$7=!B$7,OFFSET(Table1[[Choose… ]],0,0,COUNTA(Table1[[Choose… ]])),OFFSET(Table1,1,MATCH(!A8,Table1[#Headers],0)-1,COUNTA(OFFSET(Table1,0,MATCH( !A8,Table1[#Headers],0)-1,0,1))-1,1)))

      your formula like this one above (being used in a name) is re-calculated only when you change a cell that is subject to validation, so in my opinion are volatile in the same way.

      here the file i used:
      https://sites.google.com/site/e90e50/scambio-file/Dynamic-Dependent-dropdowns-one_only_name.xlsx

  11. Jeff Weir says:

    Hi Roberto. Re your comment your formula like this one above (being used in a name) is re-calculated only when you change a cell that is subject to validation, so in my opinion are volatile in the same way.

    I wasn't aware of that. Do you know why that is? I think it's strange that if you use your OFFSET formula directly in the sheet, it's volatile, but if you use it as a named range driving data validation, it's not.

    I can't fathom why that would be. I'm off to search Charles William's site for an answer 🙂

    • Hi Jeff,
      you said:
      I wasn’t aware of that. Do you know why that is?

      Answer:
      this is what you may have missed (from http://www.decisionmodels.com/calcsecretsb.htm)
      -----------------------------------------------------------
      The circumstances causing a name to be evaluated are not the same as a formula in a cell.
      1) A name seems to only be evaluated when a formula that refers to it is evaluated.
      2) A name is evaluated every time a formula that refers to it is evaluated, so that using a name in multiple formulae can cause the name to be evaluated multiple times.
      3) Names that are NOT referred to by any formula are not calculated even by a full calculation (Ctrl/Alt/F9).
      -----------------------------------------------------------
      a possible explanation for these 3 points is that in the object Name there is no a property in which the result of the calculation is stored.

      regards
      r

  12. […] Huh. All these years I’ve been telling people to avoid volatile functions in models – especially in dropdowns because large chains of dependents usually hang off of these – and it turns out that I’m wrong in that specific case, as per Roberto’s comment in this thread. […]

  13. John says:

    Hi Guys,

    Just a newbie in this and I was wondering how I can make the entry in the rows automated in my Excel - this is useful and 100 steps beyond the "normal" drop-down! Thank you! However, there is an error appearing whenever I enter the "=MainList" and "=SubList" in Data Validation: Data Validation "The list source must be a delimited list, or a reference to single......" I do not know what to do as I do not understand this error. Appreciate your help.

    John

    • Jeff Weir says:

      John: Because the SubList formula is relative, before you enter it into the Name Manager, you will need to first select cell C8, because the SubList formula as written above refers to B8 – the cell to the left. (Note that it doesn’t matter what is in C8 or where your actual dropdown are…rather it’s just that the above formula happens to refer to B8, and because we want our formula to always reference the cell on the immediate left, then we’ve got to select the cell to the immediate right before we enter this relative formula into the Name Manager.

      Also note that my version of Roger’s approach uses Excel Tables and the associated Structured References that Table functionality allows. My table is called Table1. Your validation lists MUST be held within an Excel Table (which requires Excel 2007 or greater) and you MUST change the Table1 references in the above formula to match the name of your table.

      I've amended my original post to clarify this.

      With that in mind, have another go with your sample file, and if you still can't get it, let me know and I'll fix it for you.

      And check out the post going live at 08:00 GMT on 25/2/2014 which has another possibly simpler method.

      • Parsifal1 says:

        Hi Jeff,

        First of all, thanks for your great logic to make such a dropdown list.

        I have similar problem as John. I downloaded your sample and delete the data validation function from the subcategories columns and tried to set it beck by putting "=SubList" in data validation function for subcategories columns. It has not worked and there is the error message like "The list source must be a delimited list, or a reference to single......" . What am I doing wrong cause I have not changed anything in Name Manager or so?

        I have tried also make my own dropdown list and take special care about the SubList formula to set it right as you wrote before and then set up the data validation for subcategories but there is still same error message.

        Any idea?

  14. Jeff Weir says:

    Hi John. Here's some questions:
    * What version of Excel are you using?
    * Is your list of categories (i.e. the 'Mainlist' and 'Sublist' columns) an Excel Table (i.e. you've used Excel 2007 or later, and have used the Insert Table command on a block of data)? If so, what is the name of that table? If it's anything but Table1 then you'll have to amend the formulas above accordingly.
    * Can you select say cell D5, then check what the 'Mainlist' and 'Sublist' formulas are in the name manager, and post them here?

    You can also flick me a sample file at weir.jeff@gmail.com and I'll take a look. Note that it's midnight here, so I won't be able to reply for 8 hours or so. Also note that there is a post scheduled for 8am tomorrow with a simpler method of doing cascading dropdowns, based on Roberto's comments above.

  15. […] I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like […]

  16. Aaron says:

    Love this tutorial and it's exactly what I'm needing! I'm having a problem with getting the downstream cells to clear. I'm guessing my problem is around the radio buttons since there weren't instructions on how to create that part. I figured out how to add the radio buttons and link them to a cell so you get the "1" or "2" depending on which button you clicked; however, when I have the first button selected the downstream cells do not clear when I change the value in the left most drop down menu. What are the proper steps to adding the radio buttons and getting them to work with the VBA code? I understand excel formulas, but once it comes to macros and code I have no idea what I'm doing or how to troubleshoot.

  17. Dave says:

    I looked at this code as an option for filtering through levels of management from the CEO down to level 5 managers. For all the discussion above I found a much easier solution that uses combo boxes on a form and only has a slight delay between selections. I did this...

    1. wrote an SQL query that dumped the data into a flat table on a hidden sheet with repeating values in all but the last column (much easier than the two named ranges process above)
    2. Created a pivot table on the data range (dynamic named range) in the hidden sheet
    3. created slicers for mgr 2 to 5 (don't need the CEO) in the hidden sheet
    4. created a form and 4 combo boxes for mgr2 - mgr5
    5. use the VBA Selected and Hasdata properties of the slicers and some simple loops to select/unselect the values in the relevent slicer depending on the combo box item (mgr) selected and read in the values from next slicer to populate the next combo in the list.

    Easy, fast, very simple VBA, and nothing stops users from breaking your spreadsheet better than only giving them a modal form to filter data. And no ridiculously complicated formulas for the next guy to grapple with.

    Also adding more levels is a simple job of tweaking the SQL, creating another slicer, and adding another combo and a line of code (to the generic sub that updates a combo) to the form. If more data is added in the database (i.e. more managers) then the combos update automatically as soon as I re-dump the data into the hidden sheet and redefine the named range area.

    Simple VBA, simple pivot table, and not having to face long formulas when you want to make a change!

    • jeffreyweir says:

      Glad you found an elegant solution to your business problem. I'm not sure from what you've writte above whether that business problem is the same problem as this post is intended to address. Sounds like you're doing a lot more than just providing some cascading dropdowns to me. In which case, of course the above won't be fit for purpose.

      That said, I feel duty bound to respond to your comment about 'ridiculously complicated formulas'. The formulas certainly fall into the 'Advanced' category. They certainly will be beyond people who don't 'do' advanced formulas. But then, your VBA and SQL and Slicer approach will be equally beyond those without exposure to VBA and SQL and Slicers. That doesn't mean I'd call your approach 'ridiculously complicated'.

      The above approach is pretty robust. It's also very efficient. Even better, no change whatsoever to formulas or code is required to accommodate further sub-layers. So in that regards, it's actually simpler than your approach. In fact, the code isn't even needed.

      I'm not saying my approach is better than yours, or that long formulas are better than SQL/VBA. I'm just saying that a) perhaps it's not as complicated as you think and b) perhaps it's actually just as easy for the next guy to amend on the grand scheme of things - particularly if I point the next guy to this post in my documentation.

      I use stuff like this all the time to enable admim people with no exposure to VBA and SQL to make changes to apps. Rather than adding code and slicers, all they need to do is add more information into the back-end table. It often has to be like this, because there is no 'next guy'...they need an app that a complete novice can update administrative settings in. If they need more, they hire me back, or someone else suitably qualified.

      And I write posts like this to explain my approach partly because it serves as documentation...in my file I often have a note to future developers saying "This looks complicated, but here's why I've done this, and here's a link to a post where I pick it apart piece by piece".

      Lastly, where I work, sometimes, VBA and SQL are not always an option. For instance, I'm currently working on a form that will go out to several hundred thousand external customers per year, and we know for a fact that not all of them are willing or even allowed to enable macros. In a case like that, the above approach will still work. Seamlessly.

      Also note that I am aware that setting up something like the above is pretty scary to the average user. Which is why I provide a download file...often people can better work out how to do something by playing around. Or they simply don't care about how it works, and just use my template and put their own data in accordingly. That was me 5 years ago.

      Among other projects, I'm planning on writing an add-in that has a wizard that will actually write the complicated formulas and put the data validation in place for users.

      • Dave says:

        Sorry Jeff I didn't mean 'ridiculously complicated' in a negative way, rather was just referring to your comment about your own code being a real mouthful. Such long nested formulas cane be pretty daunting when you find them in clients spreadsheets especially when they have called in help because it's broken. Also as a rule (you are the exception) the workbook usually comes with zero documentation provided - except for the end user going 'when I do this, this is supposed to happen' 😐

        Maybe it's my VB dev background but I prefer simple lines of nicely commented VBA to long nested formulas. Also there is a big chunk of VBA in the solution above to clear the downstream entries if an upstream entry is changed, so I assumed VBA *is* part of the solution. I understand that VBA isn't always an option.

        My requirement is exactly the same as above except having the simple example of fruit and veges I've got levels of managers. Once you select a 2nd level manager you only want their direct level 3 reports in the next cascaded combobox. I could fiddle the SQL to have a list of level 2 managers and a mainlist and a sublist as above, but it would be awkward SQL whereas currently it's a very simple select.

        Maybe it's because I work mostly in banks but it's very rare that I am ever having values that I can hard-code into a spreadsheet as per the example data in this solution. Invariably I'm extracting values from a database which is why I came to this page and reached the conclusion that the solution presented wasn't going to work easily for the type of real-life data I get. So I was rather stoked to find the slicer solution as an alternative and that the slicers can be manipulated via VBA and that it is very simple to create a few combos on a worksheet or form and the downstream/upstream problem is handled simply.

        So sorry I meant *for my cascading dropdown problem* I have found another solution that works.

  18. Dan says:

    Thanks for this great post. It's been very helpful to me.

    I'm having a problem with the choose... option displaying when it is not necessary. In my spreadsheet I have three subcategories, but not all possible choices require four selections for sufficient clarity.

    I would like to have the choose... option only display if there are options to choose from. However, as stated some job functions only require two selections to be sufficiently clear. In that case after the final selection is made it would be great if the choose option did not appear if there are no options to choose from.

    I'm very inexperienced with VBA and have been trying to tweak your code to give this desired result, but have had no success.

    Thanks for the help.

  19. Oluwaseun Babajide says:

    I am having problem using this code.

    One problem is that..

    Under Fruit I have another sub that is called Fruit and under fruit I have the categories you have listed above.. How can I go about this? The problem I found was that table does not take duplicate FRUITS

    • Jeff Weir says:

      Hi Oluwaseun. Apologies...have just seen your comment a year after you left it. I have another approach that you can use here, but it's pretty complicated. I'm going to code up some VBA to help people use my other approach, and put it in an upcoming book. I'll probably do a guest post here on the approach, and introducing my book at the same time.

  20. Oluwaseun Babajide says:

    I am using below formular in another tab, Sheet2, with my data startiing from A3..wHAT DO i NEED TO CHANGE?

    …and here’s the SubList formula:
    =IF(OR(Sheet1!B8="Choose…",Sheet1!B8=""),"",INDEX(Table1,1,MATCH(Sheet1!B8,Table1[#Headers],0)):INDEX(
    Table1,COUNTA(INDEX(Table1,,MATCH(Sheet1!B8,Table1[#Headers],0))),MATCH(Sheet1!B8,Table1[#Headers],0)))

  21. Will says:

    Such a great tutorial! Can't thank you enough. I'm integrating this into a spreadsheet I've been working on for a couple weeks as we speak. People's minds will be blown.
    I'll be sure to give you credit in the macros 🙂

  22. bobneurone says:

    Hi
    i downloaded the sample file Dynamic-Dependent-dropdowns-20140214
    I am using Excel 2013 french version.
    when i try to enter =SubList in the source field of data validation for any cell in G8:Z14 I get an error: "The Source list must delimit or reference one single line or column"

    What am doing wrong?

  23. bobneurone says:

    hi
    i downloaded Dynamic-Dependent-dropdowns-20140214
    i am using Excel 2013
    I get an error when i try to enter =SubList in the Source field of data validation in cells G8:Z14.
    Error says: "The Source list must delimit or reference one single column or line"

    What am i doing wrong?
    Bob

  24. Vivek Kumar says:

    Hi,
    I've tried to make the same validation but I'm unable to do...
    Is there any video for the same(How to make the same ?) ?

    Please reply..

    Vivek

  25. Amber M says:

    Thank you for your help/ post!
    I'd like to apply this VBA CODE/ tables to a dashboard I'm building.
    I have source tables on a different sheet (sheet 2), and dashboard on sheet 1.
    My real issue has been getting this code to work for DROP DOWN CELLS THAT ARE STACKED NOT SIDE BY SIDE - so my first drop down location is in cell P9 and dependent drop down is in P13. (Made them into a table, but still not working).
    Any help would be greatly appreciate - specifically help with VBA CODE EDITS/ any other suggestions for my project?
    Thanks!!!
    -Amber

  26. Jeff Weir says:

    Amber - do you mean in regards to the VBA that resets any 'orphaned' dropdowns, or the dropdowns themselves, or both? (I'm picking both).

    • Amber M says:

      I'm sort of a novice trying to figure this out for my boss :/ :p Started from very basic excel experience to building a dashboard in a month or so - thank God for sites like yours - R&D - "rob and duplicate"... So I'm going to defer to your wisdom on that. VBA code is still not something I've got a great handle on, and it is a foreign language to my IT friends.

      Let's say both?? 🙂 But the second drop down is not working so I think that's the drop down itself. (?)

      So I have a main dashboard sheet with drop downs in same column on right side...

      "Select Academic Level" <-- title above drop down
      [Bachelor's, Master's, Doctoral] <--- first drop down (main list) (cell P9)

      {2 EMPTY ROWS, then...}

      "Select Data" <--- title above second, dependent drop down
      [[sublist options*]] <-- second drop down (cell P13)

      *e.g., "One Year Retention", "Two Year Graduation", and "Four Year Graduation" for Master's, vs. "One Year Retention", "Four Year Graduation", and "Six Year Graduation" for Bachelor's.

      Want the second drop down to say/ reset to "Choose..." when first is selected just like your example file, or my dashboard says FALSE everywhere.

      I would have to source table on sheet 2; but have made the drop downs into a table as well (with no formatting) so that it hopefully will work properly. Main issue is that drop downs are in SAME column and separated by 2 rows - I've tried but can't get the code to accommodate those changes. I have to keep this formatting for functionality and design.

      THANKS AGAIN!
      -Amber

      p.s. - Totally side question... in the end I'm hoping that this dashboard can function as a macro enabled file, but also be translated to the web - do vba code/ excel files translate to javascript/the like? (they're not positive of the end user)

    • Amber M says:

      Hey Jeff, just checking back in if you're able to help with vba without clearer understanding from me - know I didn't leave much to go on!
      Thanks!
      -Amber

  27. Mark says:

    So I am racking my brain. I keep getting an error "the list source must be a delimited list" when trying to do sub-2.

    And the bizzare thing is I even manipulated the Sample File to match 100% what I have on mine. It works... but, on MY file, I get the error. The formulas in the Name Managers match exactly. I have the table names matching. I have the sheet names matching. The table data matches. Yet on mine, I get the error.

    Yes, I could just keep the manipulated file as my running file. But, just like most on here, I want to learn and figure out where I am going wrong.

    Give a man a fish - he eats for a day. Teach a man to fish, he eats forever.

    Anyone know where (probably stupidly) I went wrong?

  28. Jeff Weir says:

    To extend your fishing analogy, drop me a line via the Chandoo forum. Can you upload your sample file to http://www.chandoo.org/forum?

  29. Mark says:

    Jeff, is the Radio Buttons/Object coding necessary for the rest of the code to work?

    {New to Coding} - I can stumble my way through it. Would rather not have the object needed.

  30. Linda Suen says:

    Hi,

    It seems like if the dropdown value is a number, and the sub-cat header is a number, it won't work? I tested it added English words, and it works.

    Help!

    Also - where do I use the code? How do I apply it? Thank you!

    • Linda Suen says:

      By this, I mean:

      main list:
      shoes
      bags

      sub list:
      shoes
      boots
      flats

      sub list:
      boots
      high
      mid
      low

      This works. but if it's

      sub list:
      boots
      10
      5
      2

      It doesn't work.

      • Jeff Weir says:

        Hi Linda. Good pickup re the numbers. This is because Excel treats numbers in the headers section of a table as text, whereas it treats the numbers listed in the table itself as numbers.

        To get around this, put an apostrophe ' in front of any numbers in your sublist. But leave the apostrophe out of the column headers.

        So your sublist should look like this
        Boots
        '10
        '5
        '2

        Excel doesn't actually display the apostrophes.

        The code is optional...it forcibly clears any downstream choices should an upstream one change. If you don't need this, just leave it out.

        • Linda Suen says:

          Thank you so much! This works perfectly!

          Yes - I would love to clear downstream choices when the upstream changes. Where/how can I apply the code?

          • Jeff Weir says:

            Hi Linda. It's kinda complicated to explain, if you don't use macros a lot. You're probably best to upload your workbook to a help forum and ask for assistance. Or you can email your file to me at weir.jeff@gmail.com if you like, and I can do it for you.

  31. Wael says:

    Hi
    Thanks for the tips, however i have a question regarding the VBA code.
    If the sublists (cascading list) are below the main category and not to the right in the table, how can the code be modified to work on it?

    Thanks

    • Jeff Weir says:

      With great difficulty. I don't have time to do it at the moment, because I'm in the process of writing a book. I *might* include this in the book, if I get the time. But I'm afraid that means you're on your own.

      Maybe someone on a help forum can assist, if you get stuck. But it will require a significant recode.

  32. Linda Suen says:

    What about if List D is dependent on what is select in List B and List C? How can we do that?

  33. […] With this approach the sub-categories don’t reset when you change the main category. I found a great article at Chandoo.org that deals with that problem with some VBA code: Dynamic (Cascading) Dropdowns that reset on change […]

  34. ashish says:

    If you are interested in learning advance use of drop down list with Conditional Formatting

    http://www.exceltip.com/tips/cascading-drop-down-list-using-conditional-formatting-in-microsoft-excel-2010.html

  35. Scott M says:

    Thanks for this post...totally saved my bacon.

    I git the cascade to work like a charm in my workbook but now it seems that all sort and filter functionality doesn't work (except filter in a single, highlighted column)...Any ideas how to get all sort/filter functionality back?

  36. MO says:

    Hi, I can get the MainList dropdown to work great, but when I try to do the SubList drop down, I get the same error as a few other people: "The list source must be a delimited list, or a reference to single row or column"

    Oddly, when I downloaded your sample file and tried to check the Validation for the SubList in your file to see if I was doing something terribly different, I got the same error message when I pressed 'Ok'.

    I even changed my table and sheets to match yours and I can't seem to figure it out. Any help would be greatly appreciated.

    Thank you!

  37. Rico says:

    Hi and thanks for this awesome trick !

    I have an additional issue that I would like to clear and thus would require your help:
    The DataEntryTable that I am using includes blank cells (but not empty cell) at the end of each column list. It is because I have generated this table from a non blank free table so there is a formula returning "empty". This blank cell are understood as not empty with the SubList trick and then taken into account in the drop down list.

    How can I get rid of this blank cells ?

    Note : for those who have the "source delimited list" error message, try to select an entry in your Main data validation list prior to set the Sub data validation list in Sub category cells.

  38. MAx says:

    You are my gods!

    Many thanks for this and I wish you all the best!

    Cheers

    MAx

  39. Arun says:

    Hi..
    It is really helpful solution.
    I am facing one problem. I have inserted one column before the table (DataEntryTable). that means, now my first column of table is not the column for the dropdown or column 1 has no relation with dropdowns.
    After selecting the values in these dropdowns, if I am changing the value of the column no.1 (i.e., which is independent of this dropdown). then the value of the dropdown also is resetting. How can I overcome this issue?

  40. Olivier says:

    This is exactly want I am searching to do. Unfortunately, my "DataEntryTable" is not made of consecutive columns (I have column E, G, Y for example). And Excel does not allow me to create a Table made of non consecutive columns... :'(
    Do you have an easy way to define the DataEntryTable to keep using this macro? I will then find the things to adapt in the macro to make it function.

    Thanks a lot

  41. Philippe says:

    Hello

    Wonderful solution and nice code Thanks !
    But did you notice that if you extend DataEntryTable by dragging the blue bottom right corner, you reset all the value to "Choose..."..
    Any solution?

  42. Chuck says:

    In the Macro, how do I write the logic that clears out the rows going down not the columns across? I need my table to work in rows not columns.

    If targetCell.Column < (targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - 1) Then 'there are table cells to the right
    'For Each nextCell In targetCell.Offset(, 1).Resize(, targetCell.ListObject.ListColumns.Count + targetCell.ListObject.Range.Column - targetCell.Column - 1)
    'If HasValidationFormula(nextCell) Then
    'If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = ""
    'End If
    'Next nextCell
    End If

    'Perform different action depeding on whether we're dealing with a 'MainList' dropdown
    ' or a 'SubList' dropdown
    If HasValidationFormula(targetCell) Then
    Select Case targetCell.Validation.Formula1
    Case "=MainList"
    If targetCell.Value = "" Then
    targetCell.Value = CHOOSE
    ElseIf targetCell.Value = CHOOSE Then
    'Do nothing.
    Else
    targetCell.Offset(, 1).Value = CHOOSE
    End If

    Case "=SubList"
    If targetCell.Value = "" Then
    targetCell.Value = CHOOSE
    ElseIf targetCell.Offset(, -1).Value = CHOOSE Then
    targetCell.Value = ""
    ElseIf targetCell.Value = CHOOSE Then
    'Do nothing
    Else
    Set nextCell = targetCell.Offset(, 1)
    If HasValidationFormula(nextCell) Then
    If nextCell.Validation.Formula1 = "=SubList" Then nextCell.Value = CHOOSE
    End If
    End If
    End Select
    End If

  43. Rico says:

    Hi Jeff,

    Brillant solution !

    One question regarding the empty cells. The items of my subcategory are coming from a list containing itself in-between blank cells. I have thus used formulas to compile lists with no blank cells in my table. So basically, where in your table you have real empty cells, I have on my side formulas returning "". This "" is taken into account in the dropd down list, which is not clean.
    Would you have any idea to get rid of this blank cells ?

    Thanks

  44. First, I can see a LOT of applications for this. It really is awesome. I am trying to tweak it for a particular purpose but I am running into a problem that I can't seem to isolate.

    I am setting up a bookkeeping program. There are 2 bank accounts (personal & business). I set up two sheets & added the data entry table to both of them (different names though). I use the same "MainList" for both & the same "Table1" for both. I have two separate SubLists (SubList & SubList2). One is for the personal sheet & the other for the business sheet.

    I have this working but there is one annoying thing I can't figure out.

    On the sheet with SubList, everything works great. On the sheet with SubList2, it works well initially. But, if I change the MainCategory, Sub 1 will go back to "Choose", but Sub2 will not.

    Can anyone offer some advice? I can send the workbook to you if it would help. And, just so you know, I think I am better with Excel than I really am . . .

    Thank you very much!

    • Never mind . . . I don't really know how, but I fixed it. Basically, I just deleted the Named Range, data validation & vba. I then re-added them & it all worked.

      There must have been some small typo I wasn't seeing before.

      Now, I can say without reservation, this is awesome!

      Thank you for the education!

      • Rob says:

        Hi,

        I'm having the exact same issue. I know this is a while ago but do you remember if you were ever able to find the exact problem?

  45. Kaustubh says:

    Hi Chandoo,

    I'm looking for a 4 tier drop down list. I created one with adding names and giving indirect formula.

    The problem is names have spaces which are not accepted in the dropdowns. i want to merge data of two different variety and hence I need homogeneous names.

    Kindly help with your expertise.

    Thanks and Best
    Kaustubh Deshpande

  46. Nate Struckmeier says:

    Hi, is there an easy way to upscale dynamic cascading dropdowns? In this case, there are around 5000 items, each with their own unique color selection, when someone selects one of the 5000, then we want to see the colors available for this style item....

    Any recommendation when dealing with alot of seperate choices?

Leave a Reply