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 ?
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.
Wow! Mynda, you explained some of the most difficult formulas and made it seem very easy! This video opened my eyes to possibilities I didn't know I had. These methods will definitely save me a lot of time with large data sets when using the drop-down feature. Thank you so much for sharing your knowledge with us.
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.
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.
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)
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.
A conditional format trigger may be what you're looking for. This vid has a method towards the end: ua-cam.com/video/BpPOP1FfkP0/v-deo.html And that '#' sign. Simply magical. Makes you wonder was it always there the whole time.
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.
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.
@@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 🙈)
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!
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.
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....................... 😉✌🏼
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.
@@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.
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
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
I used to get round that by nesting a SUBSTITUTE function within the INDIRECT to replace spaces with underscores so that the INDIRECT matched my named ranges properly.
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!
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)
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?
@@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
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.
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.
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"
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
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.
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?
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.
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)))
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
Great viedo. Thanks. One minor think. Sort should be done before unique. Excel works faster that way. (source: ua-cam.com/video/FDBHoDaIaUE/v-deo.html) 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)))
Insightful as always. For some reason, my data validation is not able to accept formula =XLOOKUP(B5,$H$4:$P$4,$H$5:$P$5". Is there a reason why? there error message says "this entry leads to an error. Try entering different values to continue. would really appreciate a feedback Tracy. Thank you.
❓How do you currently manage long lists in Excel? Share your approach!
Learn Excel with my courses: bit.ly/ddlists24courses
amazing Beter for cell function
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 ?
@@shivambhola373 @MyOnlineTraingHub I use Office 2021 too and for Method 2, the Countries duplication didn't remove in drop-down list.
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
Thanks so much!
I did not know you can add # after the XLOOKUP range like that. Very interesting!
Glad you liked it!
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.
Great to hear!
Wow! Mynda, you explained some of the most difficult formulas and made it seem very easy! This video opened my eyes to possibilities I didn't know I had. These methods will definitely save me a lot of time with large data sets when using the drop-down feature. Thank you so much for sharing your knowledge with us.
I'm so glad it was helpful!
I like how easy drop down lists are becoming
Thanks for the detailed explanation
Glad it was helpful!
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.
Glad you enjoyed it!
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.
Glad you can make use of it!
This is absolutely beautiful. Struggled setting up a dashboard last week, now i can go back to it 😊
Glad it was helpful!
This is going into my top-ten Excel videos playlist. Nice!
Wow, great to hear!
wow, definitely need to practice this one. this is great!
Great to hear!
It is great ! Thanks for sharing with careful explanation ❤
Glad you liked it!
Incredible! Now I'm going to get thru ur tutorial and move 1 level up my EQ (excel Quotient)
😁 💪
You are amazing, the # outside Xlookup I have never seen 😊
Thank you so much!
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)
Yes, that would be a nice change, but I doubt we'll get it, unfortunately.
Yes, multilevel dropbox lists should def be included as a standard feature...
🤯wow that 2nd option!!!
So pleased you liked it!
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.
Yes, unfortunately they're not as robust as they could be.
Hopefully in few years time 😊
A conditional format trigger may be what you're looking for. This vid has a method towards the end:
ua-cam.com/video/BpPOP1FfkP0/v-deo.html
And that '#' sign. Simply magical. Makes you wonder was it always there the whole time.
As usual amazing video , too much information, explained in a very simple way
Thanks so much!
Great
Dependent drop down list has become easier!
So pleased you liked it!
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.
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.
@@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 🙈)
Love it. Thanks for sharing 👍👍❤❤
My pleasure!
Brilliant. Thanks for sharing!
Glad you liked it!
excellent . thank you
Glad you liked it!
This is great Mynda!
Cheers, Chris!
Thanks for your effort🌹
My pleasure!
This is magic! thanks!
Glad you like it!
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!
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.
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.......................
😉✌🏼
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.
@@MyOnlineTrainingHub You are very welcome!!! 🙏🏼🙏🏼
I am not Peter 😉Cesar is my name !!
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.
@@MyOnlineTrainingHub No problem at all !!!✌🏼😉
Thanks! so useful topic ! Gby
Glad you enjoyed it!
Thx for the knowledge
My pleasure!
FYI AL is the code for Alabama, the code for Alaska is AK.
Great tip.
Thank you!
I was going to say the same.
Great demo, @MyOnlineTrainingHub!
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.
Glad you liked it, Ian!
@@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.
Awesome to hear 😁
I like the double click instead of F9 to change the cell address
Thanks for your feedback.
That is an interesting one adding the # after the ), what other tricks are out there like this one?
😁glad you liked it, Tim!
❤ an emoji for when words can’t describe it.🤩
🥰🙏
Hi Mynda, great video.
How would I add a 4th level based on the other 3?
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
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
Hopefully, you can make use of one of the other methods that don't use INDIRECT.
I used to get round that by nesting a SUBSTITUTE function within the INDIRECT to replace spaces with underscores so that the INDIRECT matched my named ranges properly.
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!
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.
Tasmania is also a region in Australia. Misses out yet again😭
Yes, and most of the US states and UK counties. It wasn't personal, Tassie. Just a small dataset to illustrate an example 😉
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)
Yes: www.myonlinetraininghub.com/reducing-data-validation-list
Oooohh!!
It's about time this game changed!!
Hope you find it helpful, Kevin!
@@MyOnlineTrainingHub thank you... always do, Mynda!
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?
When using "address" in CELL it should be fine because it's only returning a cell reference.
@@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
excellent
Thank you!
Will the second method work in a table formatting?
I've used the 2nd method in tables, and it works the same. A life saver.
@@michaelbrown8821 sweet! Thank you! Can’t tell you how many hours I’ve spent figuring out streamlining data/lists lol 😂
I think they are the same, but, is there any advantage to using TRANSPOSE instead of TOROW?
No, both work 😉
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.
The solution to that is to use the double-click variation for method 2.
great
Glad you liked it!
The folks in Tasmania are not going to be happy!!!
😂🙈
Are they ever?
😂
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.
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"
Wow!
Thanks for watching!
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
Please see this tutorial on nested IF: www.myonlinetraininghub.com/excel-nested-ifs-explained
It is awesome, but It has one limitation. If someone clicks another cell, the filter gets erased.
Only until you select the country again, or in the case of the last example, double click.
AL=Alabama, AK=Alaska
Doh! I had a feeling I got that wrong 😂
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.
Glad you liked it! The array of arrays limit has no workaround 😁
Is there a way to have a drop down within a drop down?
No, you can't have nested dropdowns.
Thanks for your reply 😊
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?
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.
I mean the list in the drop down will be the incorrect list.
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)))
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
I just tested it and I don't get an error. Please try again with a different browser: bit.ly/ddlists24file
都不是非常完美，第一个相对实用，开发出来给别人用的时候。至少不出错。
Yes, the first one is perfect especially if you don't expect to add more data to your table.
Great viedo. Thanks.
One minor think. Sort should be done before unique. Excel works faster that way. (source: ua-cam.com/video/FDBHoDaIaUE/v-deo.html)
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)))
Thanks for sharing!
Interesting. I have always assumed that it is faster the other way.
Insightful as always.
For some reason, my data validation is not able to accept formula =XLOOKUP(B5,$H$4:$P$4,$H$5:$P$5". Is there a reason why?
there error message says "this entry leads to an error. Try entering different values to continue.
would really appreciate a feedback Tracy. Thank you.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub alright
Thank you
❤
🙏
what's # ?
Explained here: www.myonlinetraininghub.com/hash-sign-in-excel-formulas