❓How do you currently manage long lists in Excel? Share your approach! Learn Excel with my courses: bit.ly/ddlists24courses
@shakiraasfoor759920 күн бұрын
amazing Beter for cell function
@shivambhola37320 күн бұрын
Hi Mynda Treacy, I have does same working in MS Office 2021 but On part of Circular reference drop down list didn't showed up, i can see the drop down button but it didn't clicked like it has been blocked, does that function works only in Excel 365 ?
@mshi582618 күн бұрын
@@shivambhola373 @MyOnlineTraingHub I use Office 2021 too and for Method 2, the Countries duplication didn't remove in drop-down list.
@josh_excel20 күн бұрын
I did not know you can add # after the XLOOKUP range like that. Very interesting!
@MyOnlineTrainingHub20 күн бұрын
Glad you liked it!
@ismailrajiwate956521 күн бұрын
Explained the most complicated array and filter functions in a very simple way. Depended drop down list now easy to use. Thank you very much
@MyOnlineTrainingHub20 күн бұрын
Thanks so much!
@patrickschardt772421 күн бұрын
I like how easy drop down lists are becoming Thanks for the detailed explanation
@MyOnlineTrainingHub21 күн бұрын
Glad it was helpful!
@ctibornovotny559123 сағат бұрын
This is super useful for me. You can even use it in the same table where are the data for your drop down lists if you allow the user to overwrite data validation.
@MyOnlineTrainingHub19 сағат бұрын
Great to hear!
@theluckcoach459221 күн бұрын
🤯wow that 2nd option!!!
@MyOnlineTrainingHub20 күн бұрын
So pleased you liked it!
@tabithaowens34821 күн бұрын
Will the second method work in a table formatting?
@michaelbrown882120 күн бұрын
I've used the 2nd method in tables, and it works the same. A life saver.
@tabithaowens34811 күн бұрын
@@michaelbrown8821 sweet! Thank you! Can’t tell you how many hours I’ve spent figuring out streamlining data/lists lol 😂
@leelawrence33797 күн бұрын
That was pretty cool, so many ideas for it's uses now. Your tips on using the filter function and having it spill the results were a game changer in the past.
@MyOnlineTrainingHub7 күн бұрын
Glad you enjoyed it!
@Jojo-gf5qb21 күн бұрын
Great Dependent drop down list has become easier!
@MyOnlineTrainingHub20 күн бұрын
So pleased you liked it!
@jamiegoodwin666720 күн бұрын
FYI AL is the code for Alabama, the code for Alaska is AK.
@jamiegoodwin666720 күн бұрын
Great tip.
@MyOnlineTrainingHub20 күн бұрын
Thank you!
@tazguy37119 күн бұрын
I was going to say the same. Great demo, @MyOnlineTrainingHub!
@ruzaqir35421 күн бұрын
Thx for the knowledge
@MyOnlineTrainingHub21 күн бұрын
My pleasure!
@leonidasgoniotakis1648 күн бұрын
Thank you for this great and very-well explained tutorial! I have one question on 2:47 . In the data validation window , in "source" box you are inputting $H$4 and then the hash sign to reference the rest of the "horizontal array" , so my question is how does excel know that it needs to reference horizontally here ( as we want in this case of course) and not vertically (that it would be wrong for our goal) ? I hope my explanation makes sense , sorry if i missed something clarified here. Thank you in advance!
@MyOnlineTrainingHub7 күн бұрын
It simply references the array. It doesn't need to know it's horizontal or vertical. That information will be passed to it as part of the # operator.
@michaelbrown882120 күн бұрын
Thanks, Mynda. I hadn't seen any videos on this before, but got the same info from comments on a similar dropdown video, which saved my me a ton of work. Thanks for making this so I can now easily reference the technique. I have used this in Tables with little fuss. Any concerns that you can share? Great video!
@MyOnlineTrainingHub20 күн бұрын
Glad it was helpful! The only concern as with all multi-level drop downs is you can change a selection upstream that no longer relates to the downstream selections, so you may want to add some validation to highlight any occurrences of this.
@TimEllisWW20 күн бұрын
That is an interesting one adding the # after the ), what other tricks are out there like this one?
@MyOnlineTrainingHub20 күн бұрын
😁glad you liked it, Tim!
@SianDoherty16 күн бұрын
Tasmania is also a region in Australia. Misses out yet again😭
@MyOnlineTrainingHub16 күн бұрын
Yes, and most of the US states and UK counties. It wasn't personal, Tassie. Just a small dataset to illustrate an example 😉
@chrism903721 күн бұрын
This is great Mynda!
@MyOnlineTrainingHub21 күн бұрын
Cheers, Chris!
@dougbrown360721 күн бұрын
Brilliant. Thanks for sharing!
@MyOnlineTrainingHub20 күн бұрын
Glad you liked it!
@Trucpq19 күн бұрын
It is great ! Thanks for sharing with careful explanation ❤
@MyOnlineTrainingHub19 күн бұрын
Glad you liked it!
@haych-u3w11 күн бұрын
Hi Mynda, great video. How would I add a 4th level based on the other 3?
@MyOnlineTrainingHub11 күн бұрын
Replicate the last example where I created 3 levels and make a 4th. If you get 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
@yulinliu85019 күн бұрын
Love it. Thanks for sharing 👍👍❤❤
@MyOnlineTrainingHub19 күн бұрын
My pleasure!
@mouhammadwasseemshaabani4721 күн бұрын
Thanks for the best excel teacher ever , I just hope excel will consider upgrading lists so it will update the selection depending on another list selection , like to put the first option automatically as example when parent dropdown list change, (without using vba)
@MyOnlineTrainingHub20 күн бұрын
Yes, that would be a nice change, but I doubt we'll get it, unfortunately.
@BoraHorzaGobuchul19 күн бұрын
Yes, multilevel dropbox lists should def be included as a standard feature...
@melbruning20 күн бұрын
wow, definitely need to practice this one. this is great!
@MyOnlineTrainingHub20 күн бұрын
Great to hear!
@fankitty92920 күн бұрын
Thanks for the video @MyOnlineTrainingHub, super helpful. I'm wondering if there's a way it can be dynamic in the sense that it'll only show regions that haven't been selected before as the input expands? Trying to use this as a rostering tool for a small team - as such we need to have the ability to only select staff that haven't been assigned on that day and time. (Dependent factors: location, time and day as some staff can't work certain days/hours)
It is awesome, but It has one limitation. If someone clicks another cell, the filter gets erased.
@MyOnlineTrainingHub20 күн бұрын
Only until you select the country again, or in the case of the last example, double click.
@grimaldasgrydas21 күн бұрын
Thank you for the great video and handy info - very useful as always! :) I have been using the first trick (with a bit different approach) for a while now - a bit messy but works well. I have noticed that sometimes it's convenient to name both lookup arrays, and in that case you can use # for those without having to rely on pre-expansion. IMPORTANT NOTE: A word of caution, it may have been worth mentioning, that INDIRECT, OFFSET and CELL (in this case) are all *volatile* functions, so if you are using a huge set of data with the second trick, it's can and likely will cause noticeable lag, especially if you have an older/slower PC. I suppose with the new PCs it's not such a big deal, but I have a powerful PC and some really large tables in some of my projects that can easily cause issues with these functions, especially when nested.
@MyOnlineTrainingHub20 күн бұрын
Thanks for watching! In this case because we are only using INDIRECT, OFFSET and CELL in one cell, it won't have any performance implications irrespective of the size of the dataset. 😅 If we were to use them in the data validation source, then we could have issues, but thankfully that's not required here.
@grimaldasgrydas20 күн бұрын
@@MyOnlineTrainingHub Thanks for replying! I suppose I was too tired when writing my comment. I should have been more careful to point out that my point should apply only if one applies this to a *huge* set of data (referenced with these functions) AND plenty of drop-downs - just a few there and here probably wouldn't harm the performance unless the references are gigantic or the PC/Mac being old enough to have performance issues easily. I have some files where I have tables of some ~8 million cells, hundreds of drop-downs etc. In such cases my PC would certainly hiccup. In such files I'm always using a slightly different version of your first approach (named the references and made use of # so they always stay dynamic to avoid errors), mainly to avoid potential errors like forgetting to update the references when I expand the sources. By the way, do you think it could be possible, at least in theory, to add a third layer to the first approach? So far, I have deemed it impossible, but I wonder if there were tricks and workarounds to achieve that. I think I will try experimenting on it, but it will most certainly be inconveniently complicated and messy, if even possible in the first place. (Sorry for the lengthy reply 🙈)
@Petrus_Mi21 күн бұрын
This is magic! thanks!
@MyOnlineTrainingHub20 күн бұрын
Glad you like it!
@davelaff7314 күн бұрын
This is going into my top-ten Excel videos playlist. Nice!
@MyOnlineTrainingHub13 күн бұрын
Wow, great to hear!
@muhammadtambawala637920 күн бұрын
Great video Mynda, thanks. The `#` sign at the end of the formula and the cell address are both great. The issues I encounter with data validation are: A) if the text selected from the validation list is no longer in the source, it doesn’t trigger an error, so it can display text that isn't in the source list. B) Another issue is that someone can copy and paste values from another cell and data validation will still accept it.
@MyOnlineTrainingHub20 күн бұрын
Yes, unfortunately they're not as robust as they could be.
@muhammadtambawala637920 күн бұрын
Hopefully in few years time 😊
@muhammadasad72920 күн бұрын
A conditional format trigger may be what you're looking for. This vid has a method towards the end: kzbin.info/www/bejne/eKGzgINne8uehpI And that '#' sign. Simply magical. Makes you wonder was it always there the whole time.
@DecrepidOne20 күн бұрын
I love this method. Creating dynamic dependent lists has been painful. This will make it so much easier and reduces the chance of the recipient of the file mucking up the lists. Thank you.
@MyOnlineTrainingHub20 күн бұрын
Glad you can make use of it!
@stefankirst323420 күн бұрын
Thanks for these straightforward concepts! However, I personally would´t use the Cell function because it tends to break if the workbook is shared between users with different language settings. Or is there a trick to avoid that?
@MyOnlineTrainingHub20 күн бұрын
When using "address" in CELL it should be fine because it's only returning a cell reference.
@stefankirst323420 күн бұрын
@@MyOnlineTrainingHub the german version is =ZELLE("adresse") and while ZELLE will translate to CELL when an english user opens the workbook the parameter in doublequotes won´t translate properly causing the function to break
@workstuff525320 күн бұрын
I think they are the same, but, is there any advantage to using TRANSPOSE instead of TOROW?
@MyOnlineTrainingHub19 күн бұрын
No, both work 😉
@murijansulemana527019 күн бұрын
This is absolutely beautiful. Struggled setting up a dashboard last week, now i can go back to it 😊
@MyOnlineTrainingHub18 күн бұрын
Glad it was helpful!
@benbowyang21 күн бұрын
The folks in Tasmania are not going to be happy!!!
@MyOnlineTrainingHub21 күн бұрын
😂🙈
@Chris2316821 күн бұрын
Are they ever?
@kevinbush430021 күн бұрын
😂
@jorgelara311621 күн бұрын
Thanks! so useful topic ! Gby
@MyOnlineTrainingHub20 күн бұрын
Glad you enjoyed it!
@jitendratrivedi788920 күн бұрын
As usual amazing video , too much information, explained in a very simple way
@MyOnlineTrainingHub20 күн бұрын
Thanks so much!
@Excelambda18 күн бұрын
Great video !! Great trick !! ✌🏼 Remembered, 3 years ago you did "Easy Dynamic Dependent Data Validation Two Ways" and I had this comment with 5 replies: 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....................... 😉✌🏼
@MyOnlineTrainingHub18 күн бұрын
Yep, I stumbled upon your comment recently and thought I should do a video about that 😁thanks again for sharing it way back then, Peter.
@Excelambda18 күн бұрын
@@MyOnlineTrainingHub You are very welcome!!! 🙏🏼🙏🏼 I am not Peter 😉Cesar is my name !!
@MyOnlineTrainingHub13 күн бұрын
Ah, sincere apologies, Cesar! I don't know why I thought you were Peter. 😳 I've put a note in the video description with a correction.
@Excelambda13 күн бұрын
@@MyOnlineTrainingHub No problem at all !!!✌🏼😉
@ianl105215 күн бұрын
Nice one, Mynda (and Bartholomew of course). More Excel magic. I love it. I could have downloaded the file, but where's the fun in that? If it had been a large file with hundreds or even thousands of entries, that would have been a different matter, of course. I'm not lazy, but I'm not stupid either. ;-) Many thanks for your time and effort in making this video.
@MyOnlineTrainingHub14 күн бұрын
Glad you liked it, Ian!
@ianl105214 күн бұрын
@@MyOnlineTrainingHub You don't need to worry about me not downloading this particular file. I have downloaded others in the past and am, as a result, on your mailing list.
@MyOnlineTrainingHub13 күн бұрын
Awesome to hear 😁
@andrusmihail566021 күн бұрын
Buna ziua, Te urmaresc mereu cu multa, multa placere! Desi am cunostinte limitate despre excel si am 67 de ani, inteleg APROAPE TOT. Nu ma las si vreau sa stiu mai mult, asta datorita tie. Te rog sa-mi recomanzi tutorialul in care descrii cum se scriu formulele imbricate, sau functie in functie. Cu recunostinta, Sorin
@MyOnlineTrainingHub20 күн бұрын
Please see this tutorial on nested IF: www.myonlinetraininghub.com/excel-nested-ifs-explained
@kylelane63020 күн бұрын
The issue I always had, and resorted to the old convoluted method, was due to the use of the INDIRECT function in the second list in Data Validation (and therefore range names) was you couldn't have spaces in your visible text e.g. Primary_Function
@MyOnlineTrainingHub20 күн бұрын
Hopefully, you can make use of one of the other methods that don't use INDIRECT.
@dkarthikr12321 күн бұрын
Incredible! Now I'm going to get thru ur tutorial and move 1 level up my EQ (excel Quotient)
@MyOnlineTrainingHub20 күн бұрын
😁 💪
@ahmetozlemis769419 күн бұрын
I think method 2 is easily breakable, since h4 returns a list according to last cell that was edited. If i choose a country in first line and a region accoring to that, and repeat it for the second line, region dropdown menu for the first line will show the regions of the second line, because last country that was selected was in second line, not the first.
@MyOnlineTrainingHub18 күн бұрын
The solution to that is to use the double-click variation for method 2.
@dannywood745019 күн бұрын
I like the double click instead of F9 to change the cell address
@MyOnlineTrainingHub19 күн бұрын
Thanks for your feedback.
@vida171919 күн бұрын
All these methods are great. However, some users change values in the original list and then don’t understand why the chart still shows old values. The green triangles in the cells with drop-down lists indicate this issue. It would be easier if Excel automatically updated these values.
@MyOnlineTrainingHub19 күн бұрын
Sure, that would be easier, but it can't do that. You can modify the source for the first drop down to an IF formula that checks if the second drop down cell is empty. If it's not empty it only displays the text "clear region cell first". e.g. in the data validation source for the country drop down: =IF(C5="",$H$4#,$J$3) Where cell J3 contains the text "clear region cell first"
@mogarrett304521 күн бұрын
excellent
@MyOnlineTrainingHub20 күн бұрын
Thank you!
@brighttriangle19 күн бұрын
At 2:04, you mention the "Array of Arrays limitation." I tried this workaround in H4: =BYCOL(H4#,LAMBDA(curVal,FILTER(TblRegions[Region],TblRegions[Country]=curVal,""))) Sadly, that did not work. It must be the same principle. Anyway, thank you for another great video.
@MyOnlineTrainingHub19 күн бұрын
Glad you liked it! The array of arrays limit has no workaround 😁
@wiernixie20 күн бұрын
I get an error when accessing the page to download the practice file. This site can’t be reached The web page at might be temporarily down or it may have moved permanently to a new web address. ERR_FAILED
@MyOnlineTrainingHub20 күн бұрын
I just tested it and I don't get an error. Please try again with a different browser: bit.ly/ddlists24file
@MarkSmith-bj2gf20 күн бұрын
Is there a way to have a drop down within a drop down?
@MyOnlineTrainingHub19 күн бұрын
No, you can't have nested dropdowns.
@MarkSmith-bj2gf19 күн бұрын
Thanks for your reply 😊
@felixstraube27846 күн бұрын
For the second method, lets say no country or state are selected yet. Then select a country in B5, and then a state in C5. Then I go to B6 select another country, but then I decide to change the state of the previous country C5... it will show the states of the country selected in B6, not B5 as it should, wouldn't it?
@MyOnlineTrainingHub6 күн бұрын
Yes, this is how conditional formatting has always worked, so I suggest adding some conditional formatting to highlight cells that become out of sync like this so you can easily see and fix them.
@felixstraube27846 күн бұрын
I mean the list in the drop down will be the incorrect list.
@felixstraube27846 күн бұрын
With these named ranges you don't neet helper cells for the dependent dropdown Countries =TblRegions[Country] Regions =INDEX(TblRegions[[Region]:[Region]], MATCH($B5, TblRegions[[Country]:[Country]], 0)):INDEX(TblRegions[[Region]:[Region]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5))) City =INDEX(TblRegions[[City]:[City]], MATCH($B5&$C5, TblRegions[[Country]:[Country]]&TblRegions[[Region]:[Region]], 0)):INDEX(TblRegions[[City]:[City]], MATCH(2,1/(TblRegions[[Country]:[Country]]=$B5)/(TblRegions[[Region]:[Region]]=$C5)))
Great viedo. Thanks. One minor think. Sort should be done before unique. Excel works faster that way. (source: kzbin.info/www/bejne/fHWleaJ6lq6Ui6c) In this example it doesn't matter, but to get it in to the flesh memory it would be better to do it allways the correct way. Like this: =transpose(unique(sort(TblRegions[Country)))
@MyOnlineTrainingHub20 күн бұрын
Thanks for sharing!
@ahmetozlemis769419 күн бұрын
Interesting. I have always assumed that it is faster the other way.