Hi Chris. Great video! One thing I like to add is some conditional formatting to alert the user to update the dependent selection if the primary selection is changed. So, in your example, I would add conditional formatting to U2 based on =COUNTIFS(X2#,U2)=0 so that if this condition is true (i.e. there is no match), then the fill and font colors in U2 change to RED or BLACK.. something to catch the eye of the user that the selections between the two dropdowns are out of sync. This could also be made automatic with a little VBA code, but I have found that CF is sufficient to alert the user and avoid the need to go to .xlsm. Just something that I do.. others might find it useful. Thanks for the lesson and thumbs up!!
I'm looking forward to this series. I took your PowerBI course back in 2016 which is excellent. It helped me transition from marketing ops to data analytics. I became a PowerBI super user only to leave that employer for 2 others that use different visualization tools that, IMO, aren't nearly as good. The one constant that remains is Excel!
Great to hear we've been able to help you. Congrats on making your pivot in data! And absolutely, Excel is holding strong. Don't expect that to change any time soon :)
Nice video! And good to see that you guys started on UA-cam to provide help. One thing maybe is to provide downloadable material also to follow along. Thanks and keep going!
Very, very cool!!! I have been wanting to do this for some time, but there hasn't been a need (enough for the effort). Now that you have an effortless method- I look forward to utilizing this option :)
This is great to hear Sounak! Thanks for taking the time to shoot us the positive note. We are looking for crowd sourced topics, so if anything in particular is challenging for you at work, throw a question here. We just might answer it in our next video!
Great! However ideally the second list should be emptied upon changing the first list each time, otherwise it will look a bit odd and inefficient. Any solution for that? For eg. 7:02 United States is not a South Asian country.
To my knowledge you'd need to use VBA to instantaneously sync the lists without manually clicking into the list. Here's a good thread with some options, if you want to explore that route: www.mrexcel.com/board/threads/automatically-refresh-dependent-drop-down-list.1190595/
Yup, we have an entire section of our Excel Formulas & Functions course dedicated to dynamic arrays. You can find that course (along with our full library) here: www.mavenanalytics.io/online-data-analysis-courses
Hi thanks can dependent drop-down dynamically adjust so if we select south asia other drop down change dynamically so we can view those region before selecting drop-down to vii. I know it may be excel limitations but checking possibility with you. Ganesh Phutane
Good question! To my knowledge you'd need to use VBA to instantaneously sync the lists without manually clicking into the list. Here's a good thread with some options, if you want to explore that route: www.mrexcel.com/board/threads/automatically-refresh-dependent-drop-down-list.1190595/
Chris - fantastic demo video! Question: this works great with the one Excel Table. Can this be done using 2 Excel Tables? Each of the 2 Tables have 2 column, with the first column in both Tables having the same values (say, company names). The second column in the first table contains data about that company that is different from the second column in the second Table. Also, both Tables differ in the number of rows. I could combine the two Tables into 1 Table, but is there a problem if both Tables do not have the same number of rows? Hope this makes sense??
Hi Chris. Thanks for this great Video :) I have a quick question >> I tried to use the # trick with the data validation list to make it dynamic but it gives me an error (=$A$2#) The source currently evaluates to an error, Do you want to continue? I already created a table for my list. (ctrl+T) Kindly can you advise? Thanks Mina
Hi Nidhi, this file is available in the course resources for our Excel Formulas & Functions course. You can find that course (along with our full library) here: www.mavenanalytics.io/online-data-analysis-courses
Nice. The one little piece with "#" in the data validation to make the spill work is a gem.
Love you Chris from India. I have taken all your course from Udemy and that's all awesome.
Hi Chris. Great video! One thing I like to add is some conditional formatting to alert the user to update the dependent selection if the primary selection is changed. So, in your example, I would add conditional formatting to U2 based on =COUNTIFS(X2#,U2)=0 so that if this condition is true (i.e. there is no match), then the fill and font colors in U2 change to RED or BLACK.. something to catch the eye of the user that the selections between the two dropdowns are out of sync. This could also be made automatic with a little VBA code, but I have found that CF is sufficient to alert the user and avoid the need to go to .xlsm. Just something that I do.. others might find it useful. Thanks for the lesson and thumbs up!!
Thanks for adding this Wayne!
Excellent tip! impressed
Glad it’s useful Muhammad!
I'm looking forward to this series. I took your PowerBI course back in 2016 which is excellent. It helped me transition from marketing ops to data analytics. I became a PowerBI super user only to leave that employer for 2 others that use different visualization tools that, IMO, aren't nearly as good. The one constant that remains is Excel!
Great to hear we've been able to help you. Congrats on making your pivot in data!
And absolutely, Excel is holding strong. Don't expect that to change any time soon :)
Very educative ! Thanks Chris.
Glad you enjoyed it Lorenzo!
Excited for this series. This one was super helpful.
Glad you enjoyed it Colby!
So Maven!
Great stuff! Can’t wait to try it tomorrow, thanks!
Thanks for Sharing the nice solution.
Muy buenos tus cursos!!!! Gracias!!!
This is great!
Because of that, I hit subscribe. very detailed. The # got me.
Nice video! And good to see that you guys started on UA-cam to provide help. One thing maybe is to provide downloadable material also to follow along. Thanks and keep going!
Very, very cool!!! I have been wanting to do this for some time, but there hasn't been a need (enough for the effort). Now that you have an effortless method- I look forward to utilizing this option :)
Glad to hear this is useful Stacey! Hope you’ll put it to work soon :)
Great tip, I'd been struggling to work out a very similar problem, this solution worked brilliantly !
Glad to hear it Helen!
Very creative solution .
Hi Chris. This is an awesome video. It will definitely help me in my daily project work.
This is great to hear Sounak! Thanks for taking the time to shoot us the positive note. We are looking for crowd sourced topics, so if anything in particular is challenging for you at work, throw a question here. We just might answer it in our next video!
Great!
However ideally the second list should be emptied upon changing the first list each time, otherwise it will look a bit odd and inefficient. Any solution for that? For eg. 7:02 United States is not a South Asian country.
Great learning....thank you.
To my knowledge you'd need to use VBA to instantaneously sync the lists without manually clicking into the list. Here's a good thread with some options, if you want to explore that route:
www.mrexcel.com/board/threads/automatically-refresh-dependent-drop-down-list.1190595/
Thanks for the video, Sir. I would like to know if you provide dynamic arrays course?
Yup, we have an entire section of our Excel Formulas & Functions course dedicated to dynamic arrays. You can find that course (along with our full library) here: www.mavenanalytics.io/online-data-analysis-courses
@@MavenAnalytics Thanks for the reply
Hi thanks can dependent drop-down dynamically adjust so if we select south asia other drop down change dynamically so we can view those region before selecting drop-down to vii. I know it may be excel limitations but checking possibility with you.
Ganesh Phutane
Good question! To my knowledge you'd need to use VBA to instantaneously sync the lists without manually clicking into the list. Here's a good thread with some options, if you want to explore that route:
www.mrexcel.com/board/threads/automatically-refresh-dependent-drop-down-list.1190595/
Chris - fantastic demo video! Question: this works great with the one Excel Table. Can this be done using 2 Excel Tables? Each of the 2 Tables have 2 column, with the first column in both Tables having the same values (say, company names). The second column in the first table contains data about that company that is different from the second column in the second Table. Also, both Tables differ in the number of rows. I could combine the two Tables into 1 Table, but is there a problem if both Tables do not have the same number of rows? Hope this makes sense??
Hi Chris.
Thanks for this great Video :)
I have a quick question >>
I tried to use the # trick with the data validation list to make it dynamic but it gives me an error (=$A$2#)
The source currently evaluates to an error, Do you want to continue?
I already created a table for my list. (ctrl+T)
Kindly can you advise?
Thanks
Mina
Can I get excel file ?
Hi Chris why do I get #REF as an output when I use spill range operator
Is unique function not available on MS excel version 2019
Can you please provide a copy the template used in the example shared in the video? Many thanks!
Hi Nidhi, this file is available in the course resources for our Excel Formulas & Functions course. You can find that course (along with our full library) here: www.mavenanalytics.io/online-data-analysis-courses
@@MavenAnalytics Thanks so much.
when iam putting # it gives me error