Easy Dynamic Dependent Data Validation Two Ways

Рет қаралды 87,640

Күн бұрын

Dynamic Dependent Data Validation lists are easy with dynamic array formulas, but in this video I show you both with and without dynamic arrays. Download the Excel file here: www.myonlinetraininghub.com/d...
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)#
0:26 Dependent Data Validation without dynamic arrays
1:28 Setting up Data Validation Lists
1:55 Setting up depeneden Data Validation Lists with INDIRECT
3:12 Dependent Data Validation with dynamic arrays
3:25 Distinct List for Primary Data Validaton List
3:56 Secondary Lists for dependent Data Validation Lists
5:20 Setting up depeneden Data Validation Lists with XLOOKUP
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy

Пікірлер: 216

@MyOnlineTrainingHub 2 жыл бұрын

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)#

@juanmanuelpaiva6453 2 жыл бұрын

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.

@rrrprogram8667 2 жыл бұрын

You are one of the best teacher on excel

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for your kind words 😊

@syrah412 Жыл бұрын

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 KZfaq for this self-training type stuff.

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@2mdallas Жыл бұрын

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.

@MyOnlineTrainingHub Жыл бұрын

Wow, thanks for your kind words! It's greatly appreciated. Please share our channel with your friends and co-workers.

@roisinclarke2280 Жыл бұрын

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.

@MyOnlineTrainingHub Жыл бұрын

Glad I could help, Roisin!

@snicho 2 жыл бұрын

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! :)

@MyOnlineTrainingHub 2 жыл бұрын

Glad it'll be helpful!

@ekkiwurm7874 2 жыл бұрын

Thank you Mynda for sharing this video. Really well explained.

@MyOnlineTrainingHub 2 жыл бұрын

Thanks, Ekki!

@TMKJFun 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Kerry!

@wayneedmondson1065 2 жыл бұрын

Hi Mynda. Great tutorial! Thanks for showing both methods. Always nice to know multiple ways to solve the problem. Thumbs up!!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for watching, Wayne!

@mariaalcala5159 2 жыл бұрын

Great video! I really like how you can easily explain everything!

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much 😊

@IvanCortinas_ES 2 жыл бұрын

As always an excellent material. Thank you Mynda. That's very important for the job.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you think so!

@CAKimberlyLewis 11 ай бұрын

Many thanks for including the formulas in the Description! SO SO SO helpful!

@MyOnlineTrainingHub 11 ай бұрын

Glad it was helpful!

@patrickschardt7724 2 жыл бұрын

You always explain things so well in your tutorials

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for your kind words 😊

@MrEagle1982 2 жыл бұрын

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. 😊

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it, Imran!

@utubeAgape 2 жыл бұрын

Hmm, didn’t know about using a formula in the data validation source field- definitely going to play with that - thank you Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Very welcome! :-)

@bruklyn4558 2 жыл бұрын

I usually use a xlook up but this seems much more effective, thank you.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it :-)

@paulwarren3738 2 жыл бұрын

Another incredibly useful tutorial!! Thank you!!

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Paul!

@hizkiayosiepolimpung4255 2 жыл бұрын

Great video. Far simpler than the other ones i looked at.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it!

@chrism9037 2 жыл бұрын

This was excellent, thanks Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Chris!

@moisestatis5418 4 ай бұрын

THANKS SO MUCH!! very, very helpful. I was needed this.

@MyOnlineTrainingHub 4 ай бұрын

So glad! 😊

@Luciano_mp 2 жыл бұрын

Useful, easy, i liked..Thanks Mynda!

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear, Luciano!

@darrylmorgan 2 жыл бұрын

Hi Mynda!Excellent Tutorial, Really Helpful...Thank You :)

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Darryl!

@diegocastanedafiestas1574 2 жыл бұрын

So useful for me right now, thanks a lot!!

@MyOnlineTrainingHub 2 жыл бұрын

Glad it helped!

@enriquegamiz6607 2 жыл бұрын

Great Mynda it saves a lot of time, thank you

@MyOnlineTrainingHub 2 жыл бұрын

Awesome, Enrique!

@h4niali Жыл бұрын

Thank you so much, very simple and helpful

@MyOnlineTrainingHub Жыл бұрын

You're most welcome!

@cristoumanzor3896 Жыл бұрын

I was looking for this tip. Thank you!

@MyOnlineTrainingHub Жыл бұрын

Great to hear!

@elwilvanjaarsveld1694 Жыл бұрын

Absolutely Awsome - thank you very much for sharing!

@MyOnlineTrainingHub Жыл бұрын

Glad you enjoyed it!

@kamaleshvasudeva6288 2 жыл бұрын

Dashboards that I've built are using your videos. This video too will be useful. 👍

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear!

@davegoodo3603 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you'll be able to use it, Dave!

@pranavgada04 Жыл бұрын

Amazing work! Thank you!!

@MyOnlineTrainingHub Жыл бұрын

Our pleasure!

@anv.4614 10 ай бұрын

Great technique. appreciated. Thank you.

@MyOnlineTrainingHub 10 ай бұрын

Thanks for watching!

@sajij07 2 жыл бұрын

Thank you, wonderful tips

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure 😊

@grahamparker7729 2 жыл бұрын

Thanks Mynda, another great video

@MyOnlineTrainingHub 2 жыл бұрын

Thanks so much, Graham!

@taahatube1 2 жыл бұрын

This really helped thank you very much!!

@MyOnlineTrainingHub 2 жыл бұрын

Great to hear 😊

@mohamedadrar2957 2 жыл бұрын

Brilliant as usual Thank you

@MyOnlineTrainingHub 2 жыл бұрын

Glad you enjoyed it

@mandarmanik5118 2 жыл бұрын

This is simply awesome!

@MyOnlineTrainingHub 2 жыл бұрын

Glad you think so!

@paulinhofake Жыл бұрын

Very very very helpful! Many thanks :)

@MyOnlineTrainingHub Жыл бұрын

Glad to hear that!

@yulinliu850 2 жыл бұрын

Neat and clear! Thanks a lot!!!

@MyOnlineTrainingHub 2 жыл бұрын

Cheers, Yulin!

@GrzegorzReg 4 ай бұрын

Great stuff, thank you for this!😀

@MyOnlineTrainingHub 4 ай бұрын

Glad you enjoyed it!

@shakthiatukorala595 2 жыл бұрын

Nice tutorial, really useful...😃

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@Excelambda 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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.

@Excelambda 2 жыл бұрын

@@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

@Excelambda 2 жыл бұрын

"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 ?!! 😃

@MyOnlineTrainingHub 2 жыл бұрын

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 :-)

@Excelambda 2 жыл бұрын

@@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.

@erickunzel7604 Жыл бұрын

THANK YOU SO MUCH FOR THIS

@MyOnlineTrainingHub Жыл бұрын

You're so welcome!

@davidlinton4127 2 жыл бұрын

Thanks Mynda!!

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, David!

@ljudevitgaj4855 2 жыл бұрын

Thank you, great.

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it 😊

@shoaibrehman9988 2 жыл бұрын

Great stuff very useful

@MyOnlineTrainingHub 2 жыл бұрын

Glad you think so!

@zabrus24 2 жыл бұрын

Big thanks!

@MyOnlineTrainingHub 2 жыл бұрын

My pleasure, Andrew 😊

@gyozakeynsianism 2 жыл бұрын

Great video.

@MyOnlineTrainingHub 2 жыл бұрын

Thank you!

@rajanghadi2337 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

Good idea! Another time 😉

@ChopsB8X Жыл бұрын

Amazing!

@MyOnlineTrainingHub Жыл бұрын

Glad you liked it!

@katendefrancis6349 Жыл бұрын

Thank you for saving an Excel Soul once again! haha!

@MyOnlineTrainingHub Жыл бұрын

😁 pleasure!

@davidfamilydoctor9430 Жыл бұрын

I used some of these techniques recently.

@MyOnlineTrainingHub Жыл бұрын

Great to hear, David!

@drewbbc7074 2 жыл бұрын

Very nice

@MyOnlineTrainingHub 2 жыл бұрын

Thanks, Drew 😊

@rodneyplunkett6688 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

Thanks for sharing, Rodney!

@davidtopp1972 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@daviddyer2 Жыл бұрын

Thanks for this was very helpful , one question is it possible using the 365 method to make the first list box searchable also

@MyOnlineTrainingHub Жыл бұрын

With 365 we now have searchable data validation if you're on the beta channel, so yes.

@elizabethblackwell6242 Ай бұрын

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.

@MyOnlineTrainingHub Ай бұрын

😆 glad you liked it!

@simeonpayne4247 8 ай бұрын

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.

@MyOnlineTrainingHub 8 ай бұрын

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

@Norlen94 2 жыл бұрын

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?

@MyOnlineTrainingHub 2 жыл бұрын

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.

@troelstrc 2 жыл бұрын

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?

@MyOnlineTrainingHub 2 жыл бұрын

Hi Troels, please see my pinned comment for this video with the solution.

@julie_chen Жыл бұрын

Well. Thank you. This video will be saved in the “Bananas” folder. 😊

@MyOnlineTrainingHub Жыл бұрын

😁

@teoxengineer 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Searchable data validation lists are only available in Excel Online at the moment.

@rickthomas3381 2 жыл бұрын

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!!!

@MyOnlineTrainingHub 2 жыл бұрын

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.

@koikkinoik3721 Жыл бұрын

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!!

@MyOnlineTrainingHub Жыл бұрын

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

@koikkinoik3721 Жыл бұрын

@@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.

@3mro91 2 жыл бұрын

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

@kostyantynbrazhko2510 2 жыл бұрын

Hi thanks. What’s your recommendation if need to add third dependent drop down (eg. Region - country- city)?

@MyOnlineTrainingHub 2 жыл бұрын

You need to create another set of headers and lists for the third set.

@evolutionclouds 6 күн бұрын

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?

@MyOnlineTrainingHub 6 күн бұрын

Correct.

@deepakcitm 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

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 :-)

@deepakcitm 2 жыл бұрын

@@MyOnlineTrainingHub thank you 😊

@TYoung023 2 жыл бұрын

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!

@MyOnlineTrainingHub 2 жыл бұрын

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

@TYoung023 2 жыл бұрын

@@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

@momsterzz Жыл бұрын

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.

@MyOnlineTrainingHub Жыл бұрын

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

@ThaoNguyen-kg5pn 2 жыл бұрын

💚

@kausarshaikh9788 2 жыл бұрын

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

@MyOnlineTrainingHub 2 жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@angelinam6855 2 жыл бұрын

Thanks Mynda, is there a way for a user to add to the dependant list from the drop down without getting an error ?

@MyOnlineTrainingHub 2 жыл бұрын

No, you can't add to the list from the list. You have to add to the source data of the list.

@kambizradman7378 2 жыл бұрын

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.

@MyOnlineTrainingHub 2 жыл бұрын

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

@akshayashetty9880 Жыл бұрын

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

@MyOnlineTrainingHub Жыл бұрын

Not possible. You either have data validation or you don’t.

@ezmat2010 8 ай бұрын

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.

@MyOnlineTrainingHub 8 ай бұрын

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.

@yaseenmuhammad5624 2 жыл бұрын

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 2 жыл бұрын

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.

@yaseenmuhammad5624 2 жыл бұрын

@@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.

@rezaamini9830 8 ай бұрын

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.

@MyOnlineTrainingHub 8 ай бұрын

Yikes. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@icyknightster12 Жыл бұрын

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.

@MyOnlineTrainingHub Жыл бұрын

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

@RogerStocker 2 жыл бұрын

I like your videos, but I miss something in thisone. How dynamic is the secouns solution if you add a country?

@MyOnlineTrainingHub 2 жыл бұрын

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.

@HMZ89 9 ай бұрын

How would I employ the second method if I want to incorporate cities after regions and another layer of zipcodes related to cities?

@MyOnlineTrainingHub 9 ай бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@cecilgood1772 2 жыл бұрын

Excellent video , yet how to copy the formula across ?

@MyOnlineTrainingHub 2 жыл бұрын

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

@sachin.tandon 2 жыл бұрын

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?

@MyOnlineTrainingHub 2 жыл бұрын

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.

@sachin.tandon 2 жыл бұрын

@@MyOnlineTrainingHub Thanks Mynda, worth feedbacking to the MS Team. But great video and content, once again. Thank you for preparing and sharing!

@giuseppelarosa6130 Жыл бұрын

Hello, do you know a dependent data validation way that works on Excel on line?

@MyOnlineTrainingHub Жыл бұрын

Dependent data validation as shown in this tutorial works in Excel Online.

@davidtopp1972 2 жыл бұрын

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?

@MyOnlineTrainingHub 2 жыл бұрын

Hi David, I replied with a link to our forum for you to post your question and file there so we can help.

@republikadugave420 10 күн бұрын

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..

@MyOnlineTrainingHub 9 күн бұрын

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

@freepilot7732 15 күн бұрын

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?

@MyOnlineTrainingHub 15 күн бұрын

Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@freepilot7732 15 күн бұрын

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

@MyOnlineTrainingHub 15 күн бұрын

Glad I could 'help' 😜

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:

@MyOnlineTrainingHub 2 жыл бұрын

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

@tomkochka2058 2 жыл бұрын

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?

@MyOnlineTrainingHub 2 жыл бұрын

Hi Tom, please see this tutorial on absolute referencing in Tables: www.myonlinetraininghub.com/excel-table-absolute-structured-references

@NiteshKumar-lb5kv 2 жыл бұрын

maam...i want a drop down filter for pivot table..like slicer..but in drop down form...is it possible?

@MyOnlineTrainingHub 2 жыл бұрын

You can use the filter buttons on the face of the PivotTable.

@mohitkejriwal232 2 жыл бұрын

Sort, Filter etc are available only in office 365 ??

@MyOnlineTrainingHub 2 жыл бұрын

Yes.

@kuatle 2 жыл бұрын

3:12 Second method

@MyOnlineTrainingHub 2 жыл бұрын

Glad you liked it enough to tag it in your comment :-)

@user-vv3tc7yk7f 5 ай бұрын

Is there any easy way to make it work vice verse

@julianizdebski5035 Ай бұрын

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 Ай бұрын

Hmm, data validation is designed to show a unique list. You can choose the item multiple times.

@MyOnlineTrainingHub Ай бұрын

hmm, data validation is designed to show a unique list of items, but you can choose them multiple times.

@julianizdebski5035 Ай бұрын

@@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)

@MightyNationmix Жыл бұрын

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?

@MyOnlineTrainingHub Жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@MightyNationmix Жыл бұрын

@@MyOnlineTrainingHub I have posted this question on the forum, awaiting your reply

@kashirmirza6483 Жыл бұрын

How to combine two rows in data validation list?

@MyOnlineTrainingHub Жыл бұрын

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@MegaJuniorJones 2 жыл бұрын

Can you type to complete ?

@MyOnlineTrainingHub 2 жыл бұрын

In Excel online, yes. Not in the desktop yet.

@NiteshKumar-lb5kv 2 жыл бұрын

maam....how to group or filter workdays of week in pivot table...

@MyOnlineTrainingHub 2 жыл бұрын

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.

@NiteshKumar-lb5kv 2 жыл бұрын

@@MyOnlineTrainingHub thanks maam for quick reply 😊

@jackiemoeller2103 2 жыл бұрын

why doesn't formula copy to other cells

@MyOnlineTrainingHub 2 жыл бұрын

Hi Jackie, please see the pinned comment for automatic copying across columns.

@virenjethva 10 ай бұрын

I CANT USE THIS TACHNIC IN ONLINE V OF EXCEL

@MyOnlineTrainingHub 10 ай бұрын

I can't see why not 🤔 If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

@Colin-Fenix 2 ай бұрын

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?

@MyOnlineTrainingHub 2 ай бұрын

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.