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?
Fruit, anyone?
*BING!*
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!
*BING!*
Ok, so what delights does Sub Category 2 have in store for me?
Earliglow? Never heard of it. Sounds delicious…I’ll have those, please.
*BING!*
There, all done. Pretty nifty eh…users 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:
*BING!*
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:
Now watch what happens when our user subsequently decides they better vege out first:
*BING!*
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:
And I already decided on Strawberries for pudding…
But what about my main course. Ah, yes, of course…
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:
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:
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:
…and all other ‘downstream’ dropdowns – no matter what level they are – are fed by the SubList named range:
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:
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:
Then we simply list the new options for each type of meat below the relevant header:
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!
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
108 Responses to “Dynamic (Cascading) Dropdowns that reset on change”
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
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.
Also note that you don't need to update my first column any more than in your approach, unless I'm mistaken.
Jeff instead you are mistaken
You're right, I am. I'll retweak my formulas along the approach you've taken (although I'll stick with INDEX) and post back when done.
Cheers Roberto.
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
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 😀
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
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.
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.
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
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.
@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
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
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:
...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:
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
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
Hi Kenny. Probably better you post your question at Chandoo.org/forum where you can also upload a sample file.
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
FASCINATING! Thanks for this solution. I've also dealt with this and posted a video just 2 days ago.
http://youtu.be/Z77dXPoklYs
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.
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.
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.
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?
Human Meat. Sounds Tasty
Na...just like greasy chicken.
Well, human flesh is actually said to taste like a sweet pork.
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
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
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.
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
Ah, yes...I follow you now. Nothing like a second pair of eyes...especially if they are as well qualified as yours. Thanks Gabor.
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. ;))
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
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
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 😉
[…] http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/?utm_source=feedburner&u… […]
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.
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:
...which to me means the approach isn't suitable for multiple levels.
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
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
[…] 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. […]
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
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.
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?
Hi Parsifal1. I can't remember what the fix was with John. Can you post a sample file on the Chandoo Forum and post the link back here when you're done?
Hi,
Thank you for your response. Here is the thread:
http://www.chandoo.org/forum/threads/castading-drop-down-list-error-in-data-validation.28194/
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.
[…] I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like […]
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.
Aaron:
Does the sample file work?
What version of Excel do you have?
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!
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.
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.
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.
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
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.
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)))
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 🙂
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?
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
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
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
Amber - do you mean in regards to the VBA that resets any 'orphaned' dropdowns, or the dropdowns themselves, or both? (I'm picking both).
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)
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
Hi Amber. Sorry, I missed your last reply until now. Probably best you ask this over at the Chandoo forum. http://chandoo.org/forum/
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?
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?
kind of new here, wasn't sure how to PM you. But it's up. Nor did I want to call you out in the title haha.
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.
Not required, no. Will take a look at your forum post.
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!
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.
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.
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?
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.
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
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.
What about if List D is dependent on what is select in List B and List C? How can we do that?
[…] 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 […]
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
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?
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!
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.
You are my gods!
Many thanks for this and I wish you all the best!
Cheers
MAx
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?
I got solution!!! ????
Hello Arun,
I have the same problem. Did you find a solution?
Thanks
Philippe
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
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?
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
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
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!
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?
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
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?