Easy Dynamic Dependent Data Validation Two Ways - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Romell
I did the exercise with the second method and it partially worked for me ... for some reason when I filter to get my -Regions- excel shows me a -#CALC- error. The parent (Country) is listed and has more than one -region- associated in the table correctly, but for some reason it shows error. Is there a reason for excel to behave that way? It works for me for some countries but for others I have #CALC Specifically it shows me the Empty Array error, but the strange thing is that the condition is correct and there is enough data to extract in the filter ... again this behavior puzzles me ... any ideas to solve it?
PROBLEM SOVED:
reply
I did the exercise with the second method and it partially worked for me ... for some reason when I filter to get my -Regions- excel shows me a -#CALC- error. The parent (Country) is listed and has more than one -region- associated in the table correctly, but for some reason it shows error. Is there a reason for excel to behave that way? It works for me for some countries but for others I have #CALC Specifically it shows me the Empty Array error, but the strange thing is that the condition is correct and there is enough data to extract in the filter ... again this behavior puzzles me ... any ideas to solve it?
PROBLEM SOVED:
reply
David
I really like your video! I have a stand alone version of just Excel(Home & Student) I just purchased from Microsoft last month. I am trying to add the dependent data validation to an existing file. It is a checkbook register and budget, and the register has about 4500 rows and I have been using regular data validation. I am a little lost on what may be the best way to incorporate your 1st method into my file. I have 12 Parent Categories plus about 100 sub-categories in 2 separate columns.
reply
I really like your video! I have a stand alone version of just Excel(Home & Student) I just purchased from Microsoft last month. I am trying to add the dependent data validation to an existing file. It is a checkbook register and budget, and the register has about 4500 rows and I have been using regular data validation. I am a little lost on what may be the best way to incorporate your 1st method into my file. I have 12 Parent Categories plus about 100 sub-categories in 2 separate columns.
reply
David
Hello again, I wrote you about 4 hours ago, and have been trying to get the dependent data validation working in my Check Register and Budget file. I am not having any success and do not know what the problem may be. I have made named ranges for all my Parent/Sub-Categories, and have made and named all as tables, including my main check register. When I try to make data validation using the names range -Names- I receive an error. Could I email you a sample of what I am doing?
reply
Hello again, I wrote you about 4 hours ago, and have been trying to get the dependent data validation working in my Check Register and Budget file. I am not having any success and do not know what the problem may be. I have made named ranges for all my Parent/Sub-Categories, and have made and named all as tables, including my main check register. When I try to make data validation using the names range -Names- I receive an error. Could I email you a sample of what I am doing?
reply
Norlen
Thank you very much for the video. Very informative as always. Is it possible though to make the dependant drop down searchable as well? I have already done this part and it works perfectly but in my scenario, in my dependant drop down i have over 25 items to choose from. How do i make it possible for the end user to search from the available list after selecting my 1st drop down?
reply
Thank you very much for the video. Very informative as always. Is it possible though to make the dependant drop down searchable as well? I have already done this part and it works perfectly but in my scenario, in my dependant drop down i have over 25 items to choose from. How do i make it possible for the end user to search from the available list after selecting my 1st drop down?
reply
Deepak
Thank you for tutorial. It is really nice -. Just one question when you entered xlookup formula in control source for getting region name against country name there i observed that you have freezed range for country name. If my country count changes from 3 to 5 how this formula take new reference in xlookup. We have to change that manually again right?
Thanks
reply
Thank you for tutorial. It is really nice -. Just one question when you entered xlookup formula in control source for getting region name against country name there i observed that you have freezed range for country name. If my country count changes from 3 to 5 how this formula take new reference in xlookup. We have to change that manually again right?
Thanks
reply
Imran
Thanks Mynda, I had to deal with this very problem last month and ended up with a monster that included XLOOKUP, OFFSET, COUNTIFS and a couple more functions! However, my first list had more than 80 unique values and wanted to refrain from having a large number of helper columns!! Having said that, your method is a lot more elegant. -
reply
Thanks Mynda, I had to deal with this very problem last month and ended up with a monster that included XLOOKUP, OFFSET, COUNTIFS and a couple more functions! However, my first list had more than 80 unique values and wanted to refrain from having a large number of helper columns!! Having said that, your method is a lot more elegant. -
reply
Ge
please let me know if I am missing something:
When adding another country, the 2md Dropdown list does not update (because the formula was only added in 3 cells.
So this is NOT dynamic, or is there something I mised?
Everybody commenting on a great video THAT DOES NOIT WORK like expected is confusing....
reply
please let me know if I am missing something:
When adding another country, the 2md Dropdown list does not update (because the formula was only added in 3 cells.
So this is NOT dynamic, or is there something I mised?
Everybody commenting on a great video THAT DOES NOIT WORK like expected is confusing....
reply
itech
I-ve been using the old method forever, even with 365 beta. I-ve saved this to download the example when I get off the phone and onto the keyboard! Just wondering if you-ve done any quick tutorials on using the -#- symbol? I get the basic idea but would love to see some more simple examples. Thanks!
reply
I-ve been using the old method forever, even with 365 beta. I-ve saved this to download the example when I get off the phone and onto the keyboard! Just wondering if you-ve done any quick tutorials on using the -#- symbol? I get the basic idea but would love to see some more simple examples. Thanks!
reply
Kerry
Thank you Mynda - This is very useful information, concise and well explained and I will be able to apply these concepts in many settings. I really appreciate these videos - they make Excel fun to use. I will definitely share this one with my Excel friends.
reply
Thank you Mynda - This is very useful information, concise and well explained and I will be able to apply these concepts in many settings. I really appreciate these videos - they make Excel fun to use. I will definitely share this one with my Excel friends.
reply
snicho
Thanks Mynda!
I have previously used the Tables and Named Ranges for achieving this functionality; I wasn't aware of utilising Dynamic Arrays for this.
This is one that I'll definitely file in my -good stuff- collection for future reference! :)
reply
Thanks Mynda!
I have previously used the Tables and Named Ranges for achieving this functionality; I wasn't aware of utilising Dynamic Arrays for this.
This is one that I'll definitely file in my -good stuff- collection for future reference! :)
reply
Add a review, comment
Other channel videos
