To dynamically expand the regions for more countries, copy the FILTER function across more columns and use the 'not found' argument in FILTER to return blank with two double quotes, e.g.: =SORT(FILTER(TblRegions[Region],TblRegions[Country]=H4,"")) Then modify the XLOOKUP to also include further columns e.g. if your FILTER formulas are in columns H:P your XLOOKUP would be: =XLOOKUP($B5,$H$4:$P$4,$H$5:$P$5)#
Hi Mindy! How are you? I would like to ask you if you could teach how to do an excel to organize personal finances (may be with a dashboard included (?)). It would be great! Thank you in advance.
As I mentioned in comments for your "Interactive Personal Finance Dashboard" you have an ability like no other to concisely explain the essentials of each part of Excel you present. No matter the user level of proficiency you make it so people can be up and running immediately. Keep up the great work and thanks for sharing it. These are the best instructional videos I've seen.
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! :)
This was amazingly helpful! I don't have Xlookup in my 2016 version, BUT, because I was only needing a single list per drop-down, I was able to kind of 'waterfall' a functional method. You got me 99% of the way there with the Sort, Filter, Unique, and the Spill functions. I had never used them, so great work! Thanks for posting; love UA-cam for this self-training type stuff.
Cool. It would have been also great if you added creating names using headers. This would show how to create custom data validation from named ranges instead tables.
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. 😊
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.
Great video!! Cool tricks !!✌✌ To make it fully dynamic adding new countries with their regions : DV for Countries at H3#=SORT(UNIQUE(TblRegions[Country])) DV for Region at I3#=FILTER(TblRegions[Region],TblRegions[Country]=OFFSET(INDIRECT(CELL("address")),,-1)) Input data: after you select a country in B4 for example, double click in the cell to the right (C4) , click the drop down , you will get the corespondent regions. As long as the region is to the right of country DV range, you can place them anywhere
I get a #CALC! error with the FILTER formula. My solution is to wrap FILTER in IFERROR and simply copy across further columns to allow for growth. Likewise, with XLOOKUP reference more columns to allow for growth.
@@MyOnlineTrainingHub I am not sure if you get the point. Of course you have a CALC error, does not matter, the formula is looking to the left of ANY current cell. you can be anywhere on the workbook, but when you have a country to the left of your current cell and DV defined in that cell, will deliver exactly what we want. If you follow the steps , works like a charm. I always put DV arrays in other sheet , not at sight, so it can show any error , if you want , an IFERROR can solve this. ✌😉 And is super dynamic, no need of anything more, xlookup or more columns
"Input data: after you select a country in B4 for example, double click in the cell to the right (C4) , click the drop down , you will get the corespondent regions. As long as the region is to the right of country DV range, you can place them anywhere" Yo have to double click in the cell, otherwise how CELL will know where you are ?!! 😃
Ah, I missed the double clicking. That's some Excel sorcery you have there! Very clever. It's a shame it requires double clicking the cell. It's not intuitive to the user when a single click exposes the drop down, but I guess if you don't have many users it's easy to teach. Thanks for sharing :-)
@@MyOnlineTrainingHub You are very welcome !! 🙏 I am glad you make it !! 😉✌. We can tweak the "not found" argument in the FILTER to return "dbl click pls" . I prefer dbl click against expanding formulas. 😉 =SORT(FILTER(TblRegions[Region],TblRegions[Country]=OFFSET(INDIRECT(CELL("address")),,-1),"dbl clk pls")) I use this CELL gimmick everywhere, especially in conditional formatting wide tables , when I select a cell, to highlight the whole row.
And if you choose "WA" from the "Australia" list does it auto-fill to "2 hours and 20 years behind the rest of the country"? Really helpful. Didn't realise I could put formulas in the source range. Thanks so much.
I've done something similar but with only two choices I used and IF formulation in the second validation list: =IF(A42="SO",customerlist,vendorlist). Worked just fine, but as in your example the named ranges are critical.
While your tutorials are brilliant, I long for the day Excel will just walk me through building these instead of me trying to build them myself since I always end up with frustrating errors. I'm not on the same Excel wizardry level as you. I keep getting a #CALC! error every second column when I dragged this formula across, otherwise, it's very cool.
Glad you enjoy my tutorials. If you're stuck, feel free to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, Mynda, thanks for this, come to this one a bit late it seems. Tried many other videos and couldn't get this to work but yours as worked fine. Only thing I cannot get to work is when I add another item to the first list (Countries List on yours) using the # it does not dynamically show up in the validated list. Otherwise excellent.
Glad it helped, Simon! We're happy to help you get the first list dynamic if you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, I'm stuck in the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box. I press F3 but nothing happens. Even thought if I manually type the name =CountryList, it doesn't work.
Perfect, wondering if you head me in the right way, how to get rid of blanks while ticking on "ignore blank" in the validation window. I'm suffering from this.
Ignore Blank allows the user to enter a blank in the cell, it doesn't remove blanks from the source data list. You need to create a list without your blanks. If you're stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks a lot. I just have a problem with the last data list you said to add for extending the area. When I copied and pasted the filter function in the data validation list, I got the error (=,-). Thank you for your work.
Thanks Mynda for this very useful way of handling drop downs!!! The doubt that I have is, using the example you give of Countries and Regions, what happens if the user goes back to a selection already done (eg: country=Australia and region=QLD) and changes the country to UK? That does not automatically blank the region, but leaves QLD which, if the user doesn't also select the region, will be a wrong combination... I'm not sure if there is an easy solution, not having to set controls that the input combination is the correct one... Thanks and congratulations for your fantastic videos!!!
Glad it's useful to you, Rick! If someone goes back and changes a country so it doesn't make sense to the previously selected region, then you can add some conditional formatting to the region column that changes the font to red to highlight an anomaly.
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.
hello Mynda, A very special and useful technique. Thanks. The question I want to ask: How can we reduce the data in the result list as we select in the list? Can you make a tutorial video about it? Thanks
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!
The # symbol is the spilled array operator. It can be used to create a shorthand for referencing a spilled array where you use the first cell of the array and then # in your reference e.g. C1# would reference a spilled array starting in cell C1. More on dynamic arrays here: www.myonlinetraininghub.com/excel-dynamic-arrays
@@MyOnlineTrainingHub Thank you so much. I knew you could point the way! My first spreadsheet experience was with Lotus 1-2-3…so a LONG time ago. I’ve been using Excel since inception basically, but only about 15% professionally and my skills are getting rusty. I’ve even taught myself enough VBA to get by, so time to brush up. Loving some of the newer stuff like LAMBDA. Will I ever need my UDF’s again? Lol
Thank you for the video! I am double-checking on what you said in the very last part. It is not possible to use a Filter function directly in data validation, correct?
Hi Mynda, thanks for this great video. If you are placing the final data in a named table how do you get each new row in the table to automatically add the validation drop down as it is created? Thanks & best regards, Rob
All data supplying the data validation lists are in Tables that automatically include the new rows in the range. Download the example file here: www.myonlinetraininghub.com/dynamic-dependent-data-validation
Mynda, I really appreciate your prompt reply. I don't think I expressed the question very well. In your worksheet if the cell range B4:C8 was an excel table - each time that I add a new piece of data to the table a new row is created. How do I ensure that the drop downs are automatically applied to each newly created row? Thank you once again for your response. Best regards, Rob
Oh, sorry. As long as every cell in the column has the same data validation list, the table should automatically copy the data validation down as you add new rows.
Thank you very much for this. Saved me lots of time! I have a question. Is it possible to make the same for 3 rows? for example to have a Country, Region, and City, where all 3 will be dependent one from another? So basically the Region would depend on the Country, and the city would depend on the Region? Thank you in advance!!
I'm not picturing what you want. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I am truly sorry, I tried to leave a comment on your forum, however, both times the comment disappears I don't know why. Allow me to ask my question here once more, using an example. Imagine, I have 3 columns next to each other. Column 1 - Country, Column 2 - State, Column 3 - City. In Column Country I set up as you showed in your video to be able to choose for each cell from a specific list of Countries like Australia, USA, Italy etc. Then, I set up column 2 to give me options based on what I chose in Column 1. So if there I chose USA, then in column 2 I will have the states for the USA, if I choose Australia, then I will have the States of Australia etc. Now, what I want is to make column 3 - City, to give me options of cities, based on what state I chose in column 2 - State. I hope this makes sense. Thank you very much in advance.
When I tried this excel kept transposing the columns when I dragged the formula over. For example I had =SORT(FILTER(FLLlist[Employee],FLLlist[FLL]=H2)) and hen I dragged it over the next column switched to =SORT(FILTER(FLLlist[FLL],FLLlist[Employee]=I2)) and then the third column would be correct, the 4th incorrect and it kept alternating for the 80+ columns I had. Why did it do that?
Not sure which part you're referring to regarding copying across. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for this video. Can you please help me one more thing. Eg. When I select Australia i get drop down list in next column like you showed . But when I select UK i want user to write any value. How can I do this. Pls advise
Really helpfull and easy to understand, you have a new follower :-) I do have a question on how to solve an issue, as I dont find this method truly dynamic, if I add a new country and corresponding regions. The country gets added with no problem to the first dropdown, but I need to both copy the filter-formula used on the regions, AND change the xlookup-formula in the data validation to include the added columns (it seems like the # only applies to return_array's number of rows included). Do you have a toturial for this specific case?
Is there a way to combine validation list in a way tha lets say a1 is usa and b1 is australia i want in c1 all regions in australia and usa to show up..
Yes, you should be able to do that. If you get stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi Roger, you can wrap FILTER in IFERROR and then copy it across further columns to allow for growth in the number of countries. Likewise with the XLOOKUP, increase the range being looked up to incorporate more columns.
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?
This is great. This works in the sample until I try and change anything in the Formula. IE:=XLOOKUP($B5,$H$4:$J$4,$H$5:$J$5)#. This works =XLOOKUP($B5,$H$4:$K$4,$H$5:$K$5)# this causes an error and I I did was ask for it to reference an extra Column. For clarity I am using Excel D365 for the WEB The XLookup does work in a cell just not in Data Validation. Just for a test I then changed the K back to a J and and I got the same error, so this may just be that the WEB version does not like it for some reason but it will work if it is already in the cell before you import it to the WEB.
Hi you video was amazing. But I try the last part on Data Validation > input "=xlookup(......)# and it prompt me "The source currently evaluates tp am error. Do you want to continue?" Any advise for this? it would be much appreciated.
Hey, maybe this is a easy question: I did it correctly, and then I ran into a problem. My list contains multiple of the same values (ie repeating 'countries') and the list shows up as if there's a unique, but there's not. I specifically WANT the repeating values - anything I can do?
@@MyOnlineTrainingHub thanks! Didn’t know it’s ‘auto unique’ Will assign unique #’s in a helper column then adjust my data validation. Great page (as always)
Hi Mynda, Thanks for the video, but unfortunately it's not working on excel online 365, although the xlookup function is there, so do you recommend any other way to have it on excel online
Great job.... If I may ask for help? Is this possible? Could the dependant list be dynamic and it applies it to the drop down list? Example... Cell A1 is my dropdown list location. If B2=1.Then.... The drop down list (A1) will show. A, B, C to choose. BUT.... If B2=2 Then A1 drop down list will automatically change the choices to D, E, F. Continued.... But If B2=3 Then my drop down list in A1 will now show choices G, H, I, J, K. Another words your second REGION list is still dependent on your COUNTRY drop down list but isn't a list but instead the value is, let's say typed in or populated from elsewhere but again your REGION drop down list still stays dependent?
Is it possible that filter function dynamically spell as more region is added. like we don't want to copy and past the column till very left. is it possible using array formula? thanks. that will make this process dynamic forever than.
@@MyOnlineTrainingHub thanks .your new video tip of # clear a lot of thing but actually one of my client demand i need every thing dynamic that way i ask.
@@MyOnlineTrainingHub I have posted this question on the forum, awaiting your reply
2 роки тому
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:
Glad you figured it out, Romell! In future, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Is there a way this could be made more dynamic, so if you added a new country, then you don't have to include and drag across a new filter formula to detect the regions of that country?
Unfortunately, you would have to copy one formula if you add another county. This is a limitation of the filter function only for being able to spill in one direction.
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
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?
I usually show the techniques with the data visible for context when teaching. You're free to move it out of sight when implementing it in your own work.
Hi Nitesh, You can't group by week days using the PivotTable grouping. However, you can add a column to your source data that tags each row with a week number and then use that field to group the data.
To dynamically expand the regions for more countries, copy the FILTER function across more columns and use the 'not found' argument in FILTER to return blank with two double quotes, e.g.:
=SORT(FILTER(TblRegions[Region],TblRegions[Country]=H4,""))
Then modify the XLOOKUP to also include further columns e.g. if your FILTER formulas are in columns H:P your XLOOKUP would be:
=XLOOKUP($B5,$H$4:$P$4,$H$5:$P$5)#
Hi Mindy! How are you? I would like to ask you if you could teach how to do an excel to organize personal finances (may be with a dashboard included (?)). It would be great! Thank you in advance.
You are one of the best teacher on excel
Thanks for your kind words 😊
As I mentioned in comments for your "Interactive Personal Finance Dashboard" you have an ability like no other to concisely explain the essentials of each part of Excel you present. No matter the user level of proficiency you make it so people can be up and running immediately. Keep up the great work and thanks for sharing it. These are the best instructional videos I've seen.
Wow, thanks for your kind words! It's greatly appreciated. Please share our channel with your friends and co-workers.
I love you so much. You have no idea how many videos I watched for days on end, I took a break and found you. Thank you SO much.
Glad I could help, Roisin!
Hmm, didn’t know about using a formula in the data validation source field- definitely going to play with that - thank you Mynda!
Very welcome! :-)
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! :)
Glad it'll be helpful!
This was amazingly helpful! I don't have Xlookup in my 2016 version, BUT, because I was only needing a single list per drop-down, I was able to kind of 'waterfall' a functional method. You got me 99% of the way there with the Sort, Filter, Unique, and the Spill functions. I had never used them, so great work! Thanks for posting; love UA-cam for this self-training type stuff.
Great to hear!
Thank you Mynda for sharing this video. Really well explained.
Thanks, Ekki!
Many thanks for including the formulas in the Description! SO SO SO helpful!
Glad it was helpful!
Cool. It would have been also great if you added creating names using headers. This would show how to create custom data validation from named ranges instead tables.
Good idea! Another time 😉
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. 😊
Glad you liked it, Imran!
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.
Great to hear, Kerry!
Great video!! Cool tricks !!✌✌
To make it fully dynamic adding new countries with their regions :
DV for Countries at H3#=SORT(UNIQUE(TblRegions[Country]))
DV for Region at I3#=FILTER(TblRegions[Region],TblRegions[Country]=OFFSET(INDIRECT(CELL("address")),,-1))
Input data: after you select a country in B4 for example, double click in the cell to the right (C4) , click the drop down , you will get the corespondent regions.
As long as the region is to the right of country DV range, you can place them anywhere
I get a #CALC! error with the FILTER formula. My solution is to wrap FILTER in IFERROR and simply copy across further columns to allow for growth. Likewise, with XLOOKUP reference more columns to allow for growth.
@@MyOnlineTrainingHub I am not sure if you get the point. Of course you have a CALC error, does not matter, the formula is looking to the left of ANY current cell. you can be anywhere on the workbook, but when you have a country to the left of your current cell and DV defined in that cell, will deliver exactly what we want. If you follow the steps , works like a charm. I always put DV arrays in other sheet , not at sight, so it can show any error , if you want , an IFERROR can solve this. ✌😉 And is super dynamic, no need of anything more, xlookup or more columns
"Input data: after you select a country in B4 for example, double click in the cell to the right (C4) , click the drop down , you will get the corespondent regions.
As long as the region is to the right of country DV range, you can place them anywhere"
Yo have to double click in the cell, otherwise how CELL will know where you are ?!! 😃
Ah, I missed the double clicking. That's some Excel sorcery you have there! Very clever. It's a shame it requires double clicking the cell. It's not intuitive to the user when a single click exposes the drop down, but I guess if you don't have many users it's easy to teach. Thanks for sharing :-)
@@MyOnlineTrainingHub You are very welcome !! 🙏 I am glad you make it !! 😉✌. We can tweak the "not found" argument in the FILTER to return "dbl click pls" . I prefer dbl click against expanding formulas. 😉
=SORT(FILTER(TblRegions[Region],TblRegions[Country]=OFFSET(INDIRECT(CELL("address")),,-1),"dbl clk pls"))
I use this CELL gimmick everywhere, especially in conditional formatting wide tables , when I select a cell, to highlight the whole row.
You always explain things so well in your tutorials
Thanks for your kind words 😊
I usually use a xlook up but this seems much more effective, thank you.
Glad you liked it :-)
Hi Mynda. Great tutorial! Thanks for showing both methods. Always nice to know multiple ways to solve the problem. Thumbs up!!
Thanks for watching, Wayne!
As always an excellent material. Thank you Mynda. That's very important for the job.
Glad you think so!
Great video. Far simpler than the other ones i looked at.
Glad you liked it!
Dashboards that I've built are using your videos. This video too will be useful. 👍
Great to hear!
Great video! I really like how you can easily explain everything!
Thanks so much 😊
THANKS SO MUCH!! very, very helpful. I was needed this.
So glad! 😊
I was looking for this tip. Thank you!
Great to hear!
Thanks Mynda that was great. I just need to unpack it all to put it to work in an application I have in mind, thanks.
Glad you'll be able to use it, Dave!
Very very very helpful! Many thanks :)
Glad to hear that!
Thank you for saving an Excel Soul once again! haha!
😁 pleasure!
This was excellent, thanks Mynda!
Great to hear, Chris!
Useful, easy, i liked..Thanks Mynda!
Great to hear, Luciano!
Thank you so much, very simple and helpful
You're most welcome!
So useful for me right now, thanks a lot!!
Glad it helped!
And if you choose "WA" from the "Australia" list does it auto-fill to "2 hours and 20 years behind the rest of the country"?
Really helpful. Didn't realise I could put formulas in the source range. Thanks so much.
😆 glad you liked it!
Another incredibly useful tutorial!! Thank you!!
Great to hear, Paul!
Great stuff, thank you for this!😀
Glad you enjoyed it!
Great technique. appreciated. Thank you.
Thanks for watching!
This really helped thank you very much!!
Great to hear 😊
Hi Mynda!Excellent Tutorial, Really Helpful...Thank You :)
Cheers, Darryl!
Well. Thank you. This video will be saved in the “Bananas” folder. 😊
😁
Great Mynda it saves a lot of time, thank you
Awesome, Enrique!
Absolutely Awsome - thank you very much for sharing!
Glad you enjoyed it!
I used some of these techniques recently.
Great to hear, David!
This is simply awesome!
Glad you think so!
THANK YOU SO MUCH FOR THIS
You're so welcome!
I've done something similar but with only two choices I used and IF formulation in the second validation list: =IF(A42="SO",customerlist,vendorlist). Worked just fine, but as in your example the named ranges are critical.
Thanks for sharing, Rodney!
Thanks Mynda, another great video
Thanks so much, Graham!
Amazing work! Thank you!!
Our pleasure!
While your tutorials are brilliant, I long for the day Excel will just walk me through building these instead of me trying to build them myself since I always end up with frustrating errors. I'm not on the same Excel wizardry level as you. I keep getting a #CALC! error every second column when I dragged this formula across, otherwise, it's very cool.
Glad you enjoy my tutorials. If you're stuck, feel free to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Hi, Mynda, thanks for this, come to this one a bit late it seems. Tried many other videos and couldn't get this to work but yours as worked fine. Only thing I cannot get to work is when I add another item to the first list (Countries List on yours) using the # it does not dynamically show up in the validated list. Otherwise excellent.
Glad it helped, Simon! We're happy to help you get the first list dynamic if you can post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you, wonderful tips
My pleasure 😊
Brilliant as usual
Thank you
Glad you enjoyed it
Neat and clear! Thanks a lot!!!
Cheers, Yulin!
Amazing!
Glad you liked it!
Nice tutorial, really useful...😃
Glad you liked it 😊
Hi, I'm stuck in the “Source” box, hit the F3 key and select your defined name from the “Paste Name” box. I press F3 but nothing happens. Even thought if I manually type the name =CountryList, it doesn't work.
Have you enabled your function keys? If you're using a laptop, you usually have to hold down another key while pressing the function keys.
Thanks Mynda!!
My pleasure, David!
Great video.
Thank you!
Big thanks!
My pleasure, Andrew 😊
Thank you, great.
Glad you liked it 😊
Great stuff very useful
Glad you think so!
Very nice
Thanks, Drew 😊
Perfect, wondering if you head me in the right way, how to get rid of blanks while ticking on "ignore blank" in the validation window. I'm suffering from this.
Ignore Blank allows the user to enter a blank in the cell, it doesn't remove blanks from the source data list. You need to create a list without your blanks. If you're stuck, post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks a lot. I just have a problem with the last data list you said to add for extending the area. When I copied and pasted the filter function in the data validation list, I got the error (=,-).
Thank you for your work.
Yikes. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thanks Mynda for this very useful way of handling drop downs!!!
The doubt that I have is, using the example you give of Countries and Regions, what happens if the user goes back to a selection already done (eg: country=Australia and region=QLD) and changes the country to UK? That does not automatically blank the region, but leaves QLD which, if the user doesn't also select the region, will be a wrong combination... I'm not sure if there is an easy solution, not having to set controls that the input combination is the correct one...
Thanks and congratulations for your fantastic videos!!!
Glad it's useful to you, Rick! If someone goes back and changes a country so it doesn't make sense to the previously selected region, then you can add some conditional formatting to the region column that changes the font to red to highlight an anomaly.
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.
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
hello Mynda,
A very special and useful technique. Thanks.
The question I want to ask:
How can we reduce the data in the result list as we select in the list?
Can you make a tutorial video about it?
Thanks
Searchable data validation lists are only available in Excel Online at the moment.
Thanks for this was very helpful , one question is it possible using the 365 method to make the first list box searchable also
With 365 we now have searchable data validation if you're on the beta channel, so yes.
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!
The # symbol is the spilled array operator. It can be used to create a shorthand for referencing a spilled array where you use the first cell of the array and then # in your reference e.g. C1# would reference a spilled array starting in cell C1. More on dynamic arrays here: www.myonlinetraininghub.com/excel-dynamic-arrays
@@MyOnlineTrainingHub Thank you so much. I knew you could point the way! My first spreadsheet experience was with Lotus 1-2-3…so a LONG time ago. I’ve been using Excel since inception basically, but only about 15% professionally and my skills are getting rusty. I’ve even taught myself enough VBA to get by, so time to brush up. Loving some of the newer stuff like LAMBDA. Will I ever need my UDF’s again? Lol
Thank you for the video! I am double-checking on what you said in the very last part. It is not possible to use a Filter function directly in data validation, correct?
Correct.
Hi Mynda, thanks for this great video. If you are placing the final data in a named table how do you get each new row in the table to automatically add the validation drop down as it is created? Thanks & best regards, Rob
All data supplying the data validation lists are in Tables that automatically include the new rows in the range. Download the example file here: www.myonlinetraininghub.com/dynamic-dependent-data-validation
Mynda, I really appreciate your prompt reply. I don't think I expressed the question very well. In your worksheet if the cell range B4:C8 was an excel table - each time that I add a new piece of data to the table a new row is created. How do I ensure that the drop downs are automatically applied to each newly created row? Thank you once again for your response. Best regards, Rob
Oh, sorry. As long as every cell in the column has the same data validation list, the table should automatically copy the data validation down as you add new rows.
Thank you very much for this. Saved me lots of time!
I have a question. Is it possible to make the same for 3 rows?
for example to have a Country, Region, and City, where all 3 will be dependent one from another?
So basically the Region would depend on the Country, and the city would depend on the Region?
Thank you in advance!!
I'm not picturing what you want. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub
I am truly sorry, I tried to leave a comment on your forum, however, both times the comment disappears I don't know why.
Allow me to ask my question here once more, using an example.
Imagine, I have 3 columns next to each other. Column 1 - Country, Column 2 - State, Column 3 - City.
In Column Country I set up as you showed in your video to be able to choose for each cell from a specific list of Countries like Australia, USA, Italy etc.
Then, I set up column 2 to give me options based on what I chose in Column 1. So if there I chose USA, then in column 2 I will have the states for the USA, if I choose Australia, then I will have the States of Australia etc.
Now, what I want is to make column 3 - City, to give me options of cities, based on what state I chose in column 2 - State.
I hope this makes sense. Thank you very much in advance.
How would I employ the second method if I want to incorporate cities after regions and another layer of zipcodes related to cities?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
When I tried this excel kept transposing the columns when I dragged the formula over. For example I had =SORT(FILTER(FLLlist[Employee],FLLlist[FLL]=H2)) and hen I dragged it over the next column switched to =SORT(FILTER(FLLlist[FLL],FLLlist[Employee]=I2)) and then the third column would be correct, the 4th incorrect and it kept alternating for the 80+ columns I had. Why did it do that?
Hi Tom, please see this tutorial on absolute referencing in Tables: www.myonlinetraininghub.com/excel-table-absolute-structured-references
Excellent video , yet how to copy the formula across ?
Not sure which part you're referring to regarding copying across. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for this video. Can you please help me one more thing.
Eg. When I select Australia i get drop down list in next column like you showed . But when I select UK i want user to write any value.
How can I do this. Pls advise
Not possible. You either have data validation or you don’t.
Really helpfull and easy to understand, you have a new follower :-)
I do have a question on how to solve an issue, as I dont find this method truly dynamic, if I add a new country and corresponding regions.
The country gets added with no problem to the first dropdown, but I need to both copy the filter-formula used on the regions, AND change the xlookup-formula in the data validation to include the added columns (it seems like the # only applies to return_array's number of rows included).
Do you have a toturial for this specific case?
Hi Troels, please see my pinned comment for this video with the solution.
Is there a way to combine validation list in a way tha lets say a1 is usa and b1 is australia i want in c1 all regions in australia and usa to show up..
Yes, you should be able to do that. If you get stuck, please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
3:12 Second method
Glad you liked it enough to tag it in your comment :-)
Great video, i have one query when i am trying to have the 2nd drop down in another tab which is a table it is not working for me, kindly suggest
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
I like your videos, but I miss something in thisone. How dynamic is the secouns solution if you add a country?
Hi Roger, you can wrap FILTER in IFERROR and then copy it across further columns to allow for growth in the number of countries. Likewise with the XLOOKUP, increase the range being looked up to incorporate more columns.
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?
There's no built in searchable dropdown in Excel for the Desktop, but if you open the file in Excel Online it is built in already.
This is great.
This works in the sample until I try and change anything in the Formula.
IE:=XLOOKUP($B5,$H$4:$J$4,$H$5:$J$5)#. This works
=XLOOKUP($B5,$H$4:$K$4,$H$5:$K$5)# this causes an error and I I did was ask for it to reference an extra Column.
For clarity I am using Excel D365 for the WEB
The XLookup does work in a cell just not in Data Validation.
Just for a test I then changed the K back to a J and and I got the same error, so this may just be that the WEB version does not like it for some reason but it will work if it is already in the cell before you import it to the WEB.
You can’t create dynamic named ranges in Excel for the web.
Hi thanks. What’s your recommendation if need to add third dependent drop down (eg. Region - country- city)?
You need to create another set of headers and lists for the third set.
Hi you video was amazing. But I try the last part on Data Validation > input "=xlookup(......)# and it prompt me "The source currently evaluates tp am error. Do you want to continue?" Any advise for this? it would be much appreciated.
Not sure, Ray. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Is there any easy way to make it work vice verse
Hello, do you know a dependent data validation way that works on Excel on line?
Dependent data validation as shown in this tutorial works in Excel Online.
Hey, maybe this is a easy question: I did it correctly, and then I ran into a problem. My list contains multiple of the same values (ie repeating 'countries') and the list shows up as if there's a unique, but there's not. I specifically WANT the repeating values - anything I can do?
Hmm, data validation is designed to show a unique list. You can choose the item multiple times.
hmm, data validation is designed to show a unique list of items, but you can choose them multiple times.
@@MyOnlineTrainingHub thanks! Didn’t know it’s ‘auto unique’
Will assign unique #’s in a helper column then adjust my data validation.
Great page (as always)
Hi Mynda,
Thanks for the video, but unfortunately it's not working on excel online 365, although the xlookup function is there, so do you recommend any other way to have it on excel online
How to combine two rows in data validation list?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
maam...i want a drop down filter for pivot table..like slicer..but in drop down form...is it possible?
You can use the filter buttons on the face of the PivotTable.
Great job.... If I may ask for help? Is this possible?
Could the dependant list be dynamic and it applies it to the drop down list?
Example...
Cell A1 is my dropdown list location.
If B2=1.Then....
The drop down list (A1) will show. A, B, C to choose.
BUT....
If B2=2
Then A1 drop down list will automatically change the choices to D, E, F.
Continued.... But
If B2=3
Then my drop down list in A1 will now show choices G, H, I, J, K.
Another words your second REGION list is still dependent on your COUNTRY drop down list but isn't a list but instead the value is, let's say typed in or populated from elsewhere but again your REGION drop down list still stays dependent?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Thank you for your quick response. Funny how I've been trying for months but just asking and watching your video helped. I made it work. Thanks
Glad I could 'help' 😜
Is it possible that filter function dynamically spell as more region is added. like we don't want to copy and past the column till very left. is it possible using array formula? thanks. that will make this process dynamic forever than.
Hi Yaseen, the FILTER function can't spill in two directions i.e. across columns AND down rows, therefore you must copy it across to allow for growth.
@@MyOnlineTrainingHub thanks .your new video tip of # clear a lot of thing but actually one of my client demand i need every thing dynamic that way i ask.
Sort, Filter etc are available only in office 365 ??
Yes.
Thanks Mynda, is there a way for a user to add to the dependant list from the drop down without getting an error ?
No, you can't add to the list from the list. You have to add to the source data of the list.
I tried this to filter at 3 levels but results in error. E.g, select country, then select states, then within states you select province. Any help?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I have posted this question on the forum, awaiting your 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:
Glad you figured it out, Romell! In future, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Is there a way this could be made more dynamic, so if you added a new country, then you don't have to include and drag across a new filter formula to detect the regions of that country?
Unfortunately, you would have to copy one formula if you add another county. This is a limitation of the filter function only for being able to spill in one direction.
@@MyOnlineTrainingHub Thanks Mynda, worth feedbacking to the MS Team. But great video and content, once again. Thank you for preparing and sharing!
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
Hi Deepak, you can change the formula to allow for growth in the lookup ranges e.g. instead of H4:J4 enter H4:R4, then you don't need to edit it :-)
@@MyOnlineTrainingHub thank you 😊
Can you type to complete ?
In Excel online, yes. Not in the desktop yet.
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?
Hi David, I replied with a link to our forum for you to post your question and file there so we can help.
why doesn't formula copy to other cells
Hi Jackie, please see the pinned comment for automatic copying across columns.
What if I don't want all this "garbage" on my worksheet? Why don't you show how to use Tables and other worksheets (tabs) in Excel 365?
I usually show the techniques with the data visible for context when teaching. You're free to move it out of sight when implementing it in your own work.
maam....how to group or filter workdays of week in pivot table...
Hi Nitesh, You can't group by week days using the PivotTable grouping. However, you can add a column to your source data that tags each row with a week number and then use that field to group the data.
@@MyOnlineTrainingHub thanks maam for quick reply 😊