The EASIEST Excel multiple level drop down list you've ever seen! It’s multi row too!

Рет қаралды 61,311

Күн бұрын

⏬ Click to see more: plus ⚠️ important note:
Easiest Multi Level Excel Drop Down List
A newer more robust solution here Updated (now more robust)
• Updated Easiest Multi ...
One-Off dependent drop down lists in data validation are relatively straightforward: here's a technique using XLOOKUP. • XLOOKUP and easier Dep...
!!BUT!! if you want row after row of dependent lists it needs a different approach.
I've seen a lot of solutions out there that attempt to solve this, I've even done a crazy one a few months back, but this is by far the easiest technique I've come across.
⚠️ To prevent any value being typed in the dependent validation boxes you MUST UNTICK "Ignore Blanks". This in my view is a bug and will hopefully be fixed one day.
00:00 Intro
01:06 The Setup
01:50 Formula 1
=UNIQUE(tblOptions[List1])
02:53 Formula 2
=TOROW(
UNIQUE(
FILTER( tblOptions[List2], tblOptions[List1]=G6, "Empty List")))
04:50 Formula 3
=XLOOKUP(AB5, Level1Choice, Level2Result )#excel
06:14 Apply formulas to Data Validation
08:04 The Second Level List
10:36 Conditional Formatting
=COUNTIFS( XLOOKUP( I7, Level2Choice, Level3Result )#, J7)=0
You can download my workbook from the bottom of my blog post here:
accessanalytic.com.au/easiest...
----------------------------------
Thanks to Celia Alves for the inspiration. Check out Celia's video here:
• Dynamic Dependent Drop...
----------------------------------
Thanks to Erik Oehm for trying to inspire me with Lambdas ( I'll get there one day! 😁 )
Here's Erik's elegant Lambda based solution (m
aasolutions.sharepoint.com/:f...
Check out his new KZfaq channel / @excelrobot
----------------------------------
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
pbi.guide/book/
Connect with me
wyn.bio.link/

Пікірлер: 276

@AccessAnalytic 4 ай бұрын

Note, after you watch this video make sure you go and watch the updated version where I make it more robust. Here's the link kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@maxrodz8958 9 ай бұрын

After a dozen videos and hours of trying different tips on dependent drop-down lists, FINALLY got one solution that worked. THANK YOU! THANK YOU!

@AccessAnalytic 9 ай бұрын

You're welcome. Glad it solved things for you!

@reanalytics1863 3 ай бұрын

I watched the video, got excited with the solution, and forgot to comment, like and share. I only came back to do just that. Dependent validation inside excel tables had been a nightmare before I watched this video. ❤❤❤❤❤❤❤

@AccessAnalytic 3 ай бұрын

Glad it helped. Thanks for taking the time to come back and leave a kind comment

@VINAMRABHAVSAR Ай бұрын

Best Video On Multiple Dependent Dropdown I have ever seen. I was finding the solution for 2 days and finally I got one. Thank you very much!!!

@AccessAnalytic Ай бұрын

Excellent 😀. Thanks for sharing your thoughts.

@CeliaAlvesSolveExcel Жыл бұрын

Brilliant indeed, Wyn! You simplified a lot and it is just beautiful to see. Thank you for the shout-out! 💫

@AccessAnalytic Жыл бұрын

Thank you for the inspiration

@CeliaAlvesSolveExcel Жыл бұрын

@@AccessAnalytic I still have a long way to go to pay you off all the inspiration and teachings I have been receiving from you. 😉

@lizaholodovsky9326 Жыл бұрын

Hi Celia, any chance that you know how to make this work (the hash sign specifically) in google sheets?

@CeliaAlvesSolveExcel Жыл бұрын

@@lizaholodovsky9326 hi, Liza. I never tried this functionality in Google Sheets. Sorry. Have you?

@AccessAnalytic Жыл бұрын

The most likely person to know the answer is David Benaim youtube.com/@learnspreadsheets

@thegers91 Ай бұрын

I feel as though this should be one of the first lessons you are taught when learning excel. Until you have had years of experience with Excel, you don't realise the power and efficiency of good data validation to reduce errors in manual input. Thank you very much for this!

@AccessAnalytic Ай бұрын

You’re welcome.

@EricHartwigConsulting Жыл бұрын

This is really cool! I can think of so many ways to use this formula combination! Thank you Wyn for creating/sharing this and Celia for introducing this formula combination! Can not wait to put it to use!

@AccessAnalytic Жыл бұрын

Thank you. Hope it proves useful 😀

@luisgiraldes6958 2 ай бұрын

Jeeesus! I was looking for this for... ages! Congratulations for this great data dynamic validation and thank you for the great tutorial.

@AccessAnalytic 2 ай бұрын

You’re welcome. I appreciate you taking the time to let me know you found it useful

@Bhavik_Khatri Жыл бұрын

Bloody awesome! I used indirect formulas, but your new method is the winner.

@AccessAnalytic Жыл бұрын

Thanks Bhavik!

@Otis151 7 ай бұрын

Exceptional. I've been trying this different ways for over two years. This is by far the best. Great approach and great tutorial. thank you!!

@AccessAnalytic 7 ай бұрын

Glad it helped 😀

@rfdave3980 Жыл бұрын

Fantastic so very interesting. I'll have to try this out. Mind blowing. Thank you !

@AccessAnalytic Жыл бұрын

You’re welcome, thank you 😀

@marasanchez6596 3 ай бұрын

Sir you literally saved my life!! been struggling for about two days with my lists and this video finally helped me figure it out! thank you thank you!!!!!

@AccessAnalytic 3 ай бұрын

I appreciate you taking the time to let me know you found it useful

@sheripresloid7839 Жыл бұрын

After spending hours looking for what I needed, you showed up! Subscribed! This is perfect for my very detailed drop downs with many options based on the previous drop down. Because of my massive amount of combinations, you made my life so much easier! Thank you for sharing. Can't wait to look at your other videos! :)

@AccessAnalytic Жыл бұрын

Awesome! Thank you!. I appreciate you taking the time to let me know you found it useful and leaving a kind comment

@willy4masj 10 ай бұрын

Fantastic!!

@AccessAnalytic 10 ай бұрын

Cheers

@rsudeshv Ай бұрын

Thank you so much for doing this, I found you video after spending several hours trying to find a solution. This is precisely what I was looking for.

@AccessAnalytic Ай бұрын

Excellent :) Make sure you check out the slightly updated version here: kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=2hA6M9IMZWRVp_jP

@saffamike 10 ай бұрын

I'm building a complex data entry template to support month-end bulk upload routines and have been banging my head against the wall for days trying to solve my problem with data validation. Your video addressed all my issues. Thanks!!

@AccessAnalytic 10 ай бұрын

Awesome, I appreciate you taking the time to let me know you found it useful.

@ebrusalic4342 10 ай бұрын

I wish you made this video 1 year ago when I created a massive formula for a multiple dependent data validation drop down that I spent many hours and still use to date. This is incredibly beautiful. I never thought you could use # in this way.

@AccessAnalytic 10 ай бұрын

Thank you Ebru

@IvanCortinas_ES Жыл бұрын

The laboratory at full capacity. It is certainly an amazing result. Thanks Wyn!

@AccessAnalytic Жыл бұрын

Thank you Iván

@petercompton538 3 ай бұрын

This is brilliant - I tried it a couple of months ago and could not get it to work (bear of little brain syndrome strikes again) but it now does. Amazing. Big thanks to you and Celia

@AccessAnalytic 3 ай бұрын

You’re welcome Peter, glad you got it working. Make sure you check out my newer video with a little refinement to this approach. Link is in the description section

@theKOmorita 2 ай бұрын

This one helped me A LOT!! Finally something "scalable" and easy to adapt to any kind of dataset. Also, this video gave me a better understanding on how lists and array formulas interact. I experimented a bit and could come up with a solution that filling a column with specific expenses in a company (micro view), it would narrow down the options of type of expense, sector and unit/location (all the way up to a macro view)... thank you very much!!

@AccessAnalytic 2 ай бұрын

Glad it helped, I’ve also got a slightly updated version. Link is in the description. I appreciate you taking the time to let me know you found it useful

@patrickleavydatadrivenfina1491 Жыл бұрын

Thank you Wyn! This is so awesome. I've just used this for driving multiple charts for the different drilldown levels of the P&L in an interactive report 😀😀

@AccessAnalytic Жыл бұрын

Nice one Patrick !

@patrickleavydatadrivenfina1491 11 ай бұрын

@@AccessAnalytic I'm finding that in Excel Online the first dependent dropdown isn't working right (doesn't offer a dropdown) but the second one is. Do you find the same thing if you look at the file in the browser? (But it all works fine in the desktop version)

@AccessAnalytic 11 ай бұрын

@@patrickleavydatadrivenfina1491 , both are working for me online

@michelegullino478 7 ай бұрын

Thank you for this video. Clear, simple and effective!!

@AccessAnalytic 7 ай бұрын

You’re welcome

@Locomaid 2 ай бұрын

The set up of data is key. It is an „old school“ data table, not multirelational, and maybe not sexy. But … I find that many users don’t know how to correctly set up a simple table or database. Thanks for showing that it is still a relevant skill!

@AccessAnalytic 2 ай бұрын

You’re welcome

@FernandoJSantesteban 9 ай бұрын

Hi, Wyn! Thank you very much for an excellent solution to this problem! Although in 1:43 you say that setting up the validation table is a pain, for me it's the best feature. I already have a big table and need to add more rows where I wish to validate some of the columns. So I find it easy to produce a Pivot Table from the rows I already have, transform it to your validation table and use it to validate the future rows. Again, thank you so much! Kind regards from Argentina.

@AccessAnalytic 9 ай бұрын

Good stuff, thanks for the tip!

@stefiksm 5 ай бұрын

Unbelievable !! After 100 videos this is the only way tbhat worked perfectly for me!!

@AccessAnalytic 5 ай бұрын

Awesome to hear. Thanks for taking the time to let me know 😀

@PFBruno3 Ай бұрын

THANK YOU!!!! I scoured the internet of how to do this. Super simple once you see it!!

@AccessAnalytic Ай бұрын

You’re welcome. I appreciate you taking the time to let me know you found it useful

@mat.frazer 8 ай бұрын

I don't leave comments on videos but when I do... I completely gutted my solution! Thank you!

@AccessAnalytic 8 ай бұрын

You’re welcome, thanks for making an exception and commenting!

@cristian.angyal Жыл бұрын

Great video. Thanks for sharing Wyn!

@AccessAnalytic Жыл бұрын

Cheers Cristian

@shirleymoreman6725 Жыл бұрын

Thanks Wyn - brilliant stuff. It's always been hard to get these to work without using INDIRECT and complicated tables with sub levels. I have some quite volatile lists with sometimes 10-20 options in the level 2/3 lists so I'm thinking I'd probably put the level 2 and 3 choices on separate tabs so I don't get spill errors across the columns. Definitely going to have a play with this. Also, I added a check to the DV at level 2 so it returns "Clear Level 3" (text in a range I named DVErr) so the user can't change level 2 if they already put something in level 3. =IF(E14"",DVErr,XLOOKUP(C14,CatLvl1,CatLvl2)#) Of course, it won't stop people copying and pasting over the DV 🙄😂 so I've left the CF in place as well.

@AccessAnalytic Жыл бұрын

Nice Shirley. Yep separate sheets is the way to go.

@hcaseres Жыл бұрын

No English is spoken. But you make it hard as easy. Wonderful video and with amazing Excel techniques. Thank you.

@AccessAnalytic Жыл бұрын

You are very welcome. Thank you for leaving your comment

@DoskaSims3 4 ай бұрын

To be honest, I'm so so jealous of you havin Office 365. Creating this solution for office 2019 truly creates a pain in my life :D

@AccessAnalytic 4 ай бұрын

I hope it won’t be long before all organisations realise that 365 is the only way to go

@labigator 8 ай бұрын

This just saved my weekend!! This was awesome!!

@AccessAnalytic 8 ай бұрын

Glad to help ☺️

@chrism9037 Жыл бұрын

Super cool, thanks Wyn!

@AccessAnalytic Жыл бұрын

Cheers Chris

@andrewferguson7762 Жыл бұрын

loved it, thanks a lot, really well explained

@AccessAnalytic Жыл бұрын

Thank you Andrew

@CAKimberlyLewis 7 ай бұрын

Brilliant! This is exactly what I needed! My search is over!

@AccessAnalytic 7 ай бұрын

Fantastic! Thanks for taking the time to leave a kind comment

@rodyeltabaa6871 5 ай бұрын

Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then reference to the table headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊

@AccessAnalytic 5 ай бұрын

Thanks, I’ve come across a few INDIRECT methods and prefer this approach. Thanks though.

@contextmatters8243 Ай бұрын

Excellent! Crisp and Clean. 🙂 Thanks

@AccessAnalytic Ай бұрын

You’re welcome. Thanks for the feedback

@Donkeys_Dad_Adam Жыл бұрын

You should be MVP of the year!

@AccessAnalytic Жыл бұрын

😆 hah! Thanks 😊

@bernieporlares2917 2 ай бұрын

Brilliant! Thank you for this!

@AccessAnalytic 2 ай бұрын

You’re welcome

@johncipolla8335 5 ай бұрын

I have been trying to do sorts through drop downs for years. I'll be trying this step by step . I want to use this to manage my book series and keep track of how much I have read for the year and counts

@AccessAnalytic 5 ай бұрын

Hope it works out for you

@marcelloosthuyzen9118 4 ай бұрын

Love the video, thank you for sharing

@AccessAnalytic 4 ай бұрын

You’re welcome

@Luciano_mp Жыл бұрын

Great job, cool. Thank you!

@AccessAnalytic Жыл бұрын

Thank you Luciano

@elima2002 Жыл бұрын

Amazing, this is a life changing 💛

@AccessAnalytic Жыл бұрын

Hah ! 😊. Glad it helps

@Bobylly 3 ай бұрын

I needed to build something similar to this. The way I've done it is with Name Ranges and List data validation. The bad part is, the cell values were having special characters, so I had to create an intermediary table just to handle this. Now, this approach looks much faster, I'm sure I'm gonna at least try it out! 🔥🔥🔥🔥🔥🔥🔥

@AccessAnalytic 3 ай бұрын

I hope it works out for you 👍🏼

@mclainekeari5787 2 ай бұрын

Simply beautiful!

@AccessAnalytic 2 ай бұрын

Thanks for taking the time to leave a kind comment

@CashxE 7 ай бұрын

Save me tons of hours great stuff, the BI guy in my company said its impossible task via Excel.

@AccessAnalytic 7 ай бұрын

Glad to help 😃

@martinargimon730 Жыл бұрын

hi. i just took the liberty of quoting tht link to another way of doing a dependant drop down. Thanks Wyn anyway. Your videos are a masterpiece. Kind regards. martin

@AccessAnalytic Жыл бұрын

Very kind of you to say so 😊, thanks Martin

@AccessAnalytic Жыл бұрын

Hi Martin, not sure what you mean by the quoting link comment

@ankitjainsss 25 күн бұрын

wow, what a great formula. Thnakyou

@AccessAnalytic 25 күн бұрын

You’re welcome. 😀

@ranildavantes7495 Ай бұрын

I have been looking for this formula for a long time and I am glad I found your KZfaq tutorial. Your explanation is very clear and easy to understand. This is the formula I created =TOROW(FILTER(ProjFunding[Funding source ID],ProjFunding[Project contract ID]=M2,"Missing")) and it works well. However, when I copy the formula to the validation list, it is not recognizing this formula and getting "1+1" error message. I would appreciate if you can provide guidance how to resolve this issue. Thanks in advance!

@AccessAnalytic Ай бұрын

Thanks. The list input box can only accept a formula that returns a range. FILTER is not a function that does that while XLOOKUP is

@ranildavantes7495 Ай бұрын

@@AccessAnalytic Appreciate your reply. I used the XLookup function, and it shows the same behavior as show on your video except for the "#" character embedded at the end of the formula. I am getting a #REF error message. Is there any settings in Excel that I need to change?

@AccessAnalytic Ай бұрын

No settings to change. You say you’re not using the # - is there a reason?

@ricardomantovaniassis 6 ай бұрын

Thanks Wyn, amazing video. 🙏🙏🙏🙏

@AccessAnalytic 6 ай бұрын

Cheers 😀

@HamdanYouTuber Жыл бұрын

Useful video, thanks

@AccessAnalytic Жыл бұрын

You’re welcome

@tarek.grisha Ай бұрын

Many Thanks. That is brilliant

@AccessAnalytic Ай бұрын

Thanks, you're welcome

@sledgehammer-productions 4 ай бұрын

Hang on, wasn't this the challenge that you couldn't solve easily in February 2023? Way to go with all these new formulas that Excel is giving us. The conditional formatting I constructed myself about a month ago, glad to see I did it with the same method. Would have saved me some time if I had know about this video. By the way, for $$ in cel references we have F4, is there a shortcut to get the #?

@AccessAnalytic 4 ай бұрын

Yep, was inspired by another MVPs solution that gave the key part to solving this

@sanchowitfurrows1561 9 ай бұрын

just wow! complete solution, like it

@AccessAnalytic 9 ай бұрын

Thank you. I appreciate you taking the time to let me know you found it useful

@sofiacerecero6145 11 ай бұрын

My head exploded! Thank you so much for this!

@AccessAnalytic 11 ай бұрын

You're welcome, I think 🤣

@anakara6967 Жыл бұрын

Hi Wyn, I was searching some days to find find an easy way !! - Thanks a lot - you're amazing !!. can you give me something to make this drop down list searchable? Thanks again😃

@AccessAnalytic Жыл бұрын

The good news is it’s searchable in a excel for web and in the latest insider release of desktop… the bad news is you will need to wait 2-6 months depending on your “update channel” of M365

@alkapaul8432 Жыл бұрын

Big thanks it was amazing 😊

@AccessAnalytic Жыл бұрын

You're welcome 😀

@Ojassonnisify 10 ай бұрын

THANK YOU!

@AccessAnalytic 10 ай бұрын

You're welcome

@Fabi_terra 4 ай бұрын

Hi there Wyn, I wanted to reach out and let you know that I got to your channel after being referred by Celia. I must say, your tutorial was incredible. The way you explained everything was fantastic and simple to understand. 😊😊 I had a question though, is it possible to add a function to the formulas which prevents someone from entering the same data twice in the source list?

@AccessAnalytic 4 ай бұрын

Thanks, and thanks to Celia 😀. You can add conditional formatting to highlight duplicates or a formula that checks if there are duplicates. Not sure about preventing duplicates. Maybe a a variant of COUNTIF > 1 inside the validation

@Fabi_terra 4 ай бұрын

@@AccessAnalytic Thank you so much!

@rajuj2548 5 ай бұрын

Super Information 👌

@AccessAnalytic 5 ай бұрын

Thanks, much appreciated.

@grahamc5531 Жыл бұрын

And there's me thinking Die Hard was a Christmas movie...great technique though!

@AccessAnalytic Жыл бұрын

Next video..,, how to handle movies in multiple categories 🤣

@ameliatantri 6 ай бұрын

THANK YOU SO MUCH, IT REALLY HELPS A LOT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111111

@AccessAnalytic 6 ай бұрын

You’re welcome 😀

@patrickoconnell7124 3 ай бұрын

Hi, excellent video. Can you tell me what excel functions are directly supported in the Data Validation Allow: & Source: field boxes..Thank you

@AccessAnalytic 3 ай бұрын

I’m honestly not sure sorry.

@danpostica898 10 ай бұрын

Thank YOU sooooooo much

@AccessAnalytic 10 ай бұрын

😀 you’re welcome

@juja2819 8 ай бұрын

Thank you 😀

@AccessAnalytic 8 ай бұрын

You’re welcome

@sangramdash935 7 ай бұрын

Excellent 👍

@AccessAnalytic 7 ай бұрын

Cheers

@samiajaved1834 6 ай бұрын

man you are awesome

@AccessAnalytic 6 ай бұрын

Hah 😊, too kind!

@abdulsaidmkamara7305 Жыл бұрын

This is beautiful technique , does the hash sign at the end make any formula return an array ??

@AccessAnalytic Жыл бұрын

Thanks, no only those that return a range ( little known fact that XLOOKUP returns a range ). Will work with INDEX

@kafeelshaikh1561 7 ай бұрын

Awesome video❤...How many dependent drop down lists can we create using this method? I wanted to create drop down lists to 7 level choice ( 7 columns)

@AccessAnalytic 7 ай бұрын

Thanks, it’s unlimited really

@arvindkumarauro9731 3 ай бұрын

Amazing ❤❤

@AccessAnalytic 3 ай бұрын

Thank you 😀

@EricaDyson Жыл бұрын

Fantastico!

@AccessAnalytic Жыл бұрын

Thanks Erica! 😀

@w.muhammad4030 6 ай бұрын

it is the so far work arable thanks

@AccessAnalytic 6 ай бұрын

You’re welcome

@Funkhowzer 9 ай бұрын

This is amazing and is literally the only way I’ve been able to make this work. One question. Column 2 of the selection table contains duplicate values, so the Drop Down list for column 3 of the input table is displaying all values associated with the duplicate value. Is there a way to filter column 3 based off not only selection 2, but selection 1 as well?

@AccessAnalytic 9 ай бұрын

There is a way that I found after someone else pointed out the same thing. I need to do another video on it in the next few weeks.

@AccessAnalytic 9 ай бұрын

The simplest ( non ideal ) quick fix is to avoid duplicates via a prefix / extra space

@AccessAnalytic 4 ай бұрын

Took more than a few weeks of waiting ( 4 months! ) but update is here kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@s1ngularityxd64 Жыл бұрын

awesome🤩

@AccessAnalytic Жыл бұрын

Thank you!

@lesterpotts6142 Жыл бұрын

Hi Wyn, first time commenting on your channel. My issue is that if you add new or a different set of data to the table, I don't see the drop down list update dynamically. The beauty of spill arrays is that things like drop down list should be dynamic.

@AccessAnalytic Жыл бұрын

Hi, the drop downs should contain new options if data is changed in the table. Yes it’s annoying that the 2nd formula doesn’t spill, but that formula could be copied down 100 or more rows in advance.

@willemmulder7304 5 ай бұрын

Hi Wyn, great solution! I have a question. Is it possible when I select an item under Dependent, that the first item automatically appears under Sub Dependent? So that an invalid value does not appear first? Looking forward to your solution. :) Kind Regards, Willem Mulder

@AccessAnalytic 4 ай бұрын

Thanks, sounds like the type of thing that would need some VBA.

@ezelkarlkl1284 Жыл бұрын

like a magic ! :)

@AccessAnalytic Жыл бұрын

🪄✨

@0rtli 10 ай бұрын

An excellent resolution, I truly appreciate it! Nonetheless, there are remaining matters I need to address. In my scenario, there are four columns: the first serves as the primary column, the second relies on the first column, the third is contingent upon a combination of the first and second columns, and the fourth hinges on the interplay of the first, second, and third columns. Do you have any insights into how I can apply your solution in this complex situation?

@0rtli 10 ай бұрын

I will personally address my inquiry, wherein I combine tables using the " | " operator and place them into named ranges named Level2Choice and Level3Choice. Subsequently, within the Data Validation, I employ the following formula: =XLOOKUP(D5 & " | " & E5, Level2Choice, Level3Result) #. - Hope this will be helpful for someone.

@AccessAnalytic 10 ай бұрын

Glad you solved it

@AccessAnalytic 4 ай бұрын

@@0rtli : check it out kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@tarek.grisha Ай бұрын

I LOVE YOU

@AccessAnalytic Ай бұрын

🤣

@lizaholodovsky9326 Жыл бұрын

This is awesome! Is there any way to do the same thing in Google Sheets?

@AccessAnalytic Жыл бұрын

Thanks 😀, I’ve not used Google sheets sorry.

@lindseyanderson2800 11 ай бұрын

This is amazing, but I'm still having problems. Can you help? Based on your video, I've developed this formula =SORT(UNIQUE(FILTER(AnnualStmt[Property],(AnnualStmt[Grant]=DataEntry[Grant])*(AnnualStmt[Title]=DataEntry[Title])))), which works perfectly until I try to put it into Data Validation. The xlookup portion of your formula doesn't seem to work when combining two lookups. Any thoughts how I can get this to work?

@AccessAnalytic 11 ай бұрын

Thanks, Not sure sorry. There’s lots of little traps and restrictions when trying to get this to work

@tamersalem7542 2 ай бұрын

Thank you for your interest in spreading knowledge. I had an opinion regarding Dynamic Dynamics at minute 3:40 I think there's a lot of potential for this with dynamic arrays. Let me know what you think.

@AccessAnalytic 2 ай бұрын

I’m not sure what your question is?

@tamersalem7542 2 ай бұрын

@@AccessAnalytic I mean, there is a solution to create an automatic filtering function, which you will explain in a minute ٣:٤٠

@AccessAnalytic 2 ай бұрын

@@tamersalem7542 ah... is that using MAP/MAKEARRAY or some similar technique?

@tamersalem7542 Ай бұрын

@@AccessAnalytic Can you help me get the solution? Because I tried the equations that were mentioned, I did not get a solution. Please solve them because they are required in a practical way. Thank you for your kindness.

@AccessAnalytic Ай бұрын

Sorry I don’t understand what the problem is

@retozimmermann5391 5 ай бұрын

This solved half of my problem. What can I do, when the result of list3 depends on the combination of list1 and list2 ?

@AccessAnalytic 5 ай бұрын

Hi, take a look at the comment by Tiffany, where they explain an approach

@AccessAnalytic 4 ай бұрын

Here's my updated solution kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@SBDH070 4 ай бұрын

Great video. Is it possible to set it up so that only the values in the list are allowed? This only works with the first column (the non dependant column). Thanks!

@SBDH070 4 ай бұрын

If validation doesnt work for the xlookup columns you need to uncheck the ignore blanks option.

@AccessAnalytic 4 ай бұрын

Glad you worked it out. It was on my list to double check and come back to you.

@AccessAnalytic 4 ай бұрын

@@SBDH070 : Check it out: kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@tiffanydrouin2622 7 ай бұрын

This looks great! The main issue I'm having is that data validation won't let me remove the absolute references without giving an error. $A$3 works fine in the data validation list but using $A3 or A3 it says "This entry leads to an error. Try entering different values to continue." and will not let me proceed. It's done this with several techniques now. Any idea what is going on? Edit: I finally figured out what was going on. I had been using the online version of 365, which has limitations and reduced capabilities. When I swapped over to using the desktop version of 365 this method worked perfectly. THIS IS A GAME CHANGER! THANK YOU THANK YOU THANK YOU!!

@AccessAnalytic 7 ай бұрын

Not sure sorry. Might be worth posting some screenshots to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 Or to www.reddit.com/r/excel/

@tiffanydrouin2622 6 ай бұрын

@@AccessAnalytic I was able to figure it out. It was because I was using the online version of 365. When I swapped to using the desktop version of 365 this method worked beautifully! Turns out the online version has many limitations including not having access to Name Manager, which is what ended up tipping me off that the online v desktop versions have different capabilities/limitations. Thank you so so much for this video! This has saved me much grief.

@tiffanydrouin2622 6 ай бұрын

@@AccessAnalytic It was also fairly easy to adapt this to where the 3rd level choice is narrowed down by BOTH the 1st and 2nd choices. In my case, a single supplier (2nd level) can provide materials to multiple jobs (1st level) the PO# (3rd level) not only needs to be associated with the supplier but with the specific job too. The formula you gave shows all PO#s for a supplier regardless of what job the POs are associated with. By adding the *(tbl_SupplierInfo[Job'#]=A4) section to the formula it easily filters out supplier PO#s associated with other jobs and gives me the supplier's POs for the specific job I'm entering data. The *(tbl_Invoices[@Supplier]"") part prevents 0s from showing in the PO selection when someone has entered a job# but not a supplier. Adjusted formula: =TRANSPOSE(SORT(UNIQUE(FILTER(tbl_SupplierInfo[PO'#],(tbl_SupplierInfo[Supplier]=$C4)*(tbl_Invoices[@Supplier]"")*(tbl_SupplierInfo[Job'#]=$A4),"Select supplier first")))) I put this formula on each line to the right of my data entry table then entered =$AS4# in the data validation list formula bar. Works great! Example data used for dropdowns is below: Job# Supplier PO# 23-M-050 Plumbers Supply Company 22-123-456 23-M-050 Trane Technologies 22-987-654 23-M-022 Blackmore & Glunt 1234567-0-CONS 23-M-018 Blackmore & Glunt 9876543-0-CONS 22-M-115 Plumbers Supply Company 1122334-0-CONS 22-M-115 Trane U.S. Inc. 2233445-0-CONS 22-M-114 Plumbers Supply Company 3344556-0-CONS 22-M-114 Stoermer-Anderson Inc. 4455667-0-CONS 22-M-094 Blackmore & Glunt PO1111-OC 22-M-094 Koch Air - Louisville PO2222-OC 22-M-094 Masters' Supply Inc. PO3333-OC

@AccessAnalytic 6 ай бұрын

@tiffanydrouin2622 - glad you figured it out 😀

@AccessAnalytic 6 ай бұрын

Great stuff

@someguy2972 6 ай бұрын

Righto, well I've been scouring the internet to solve this for a long time, lets see if your vid leads to a subscribe.

@AccessAnalytic 6 ай бұрын

🤷🏻‍♂️

@alexsandor4673 Жыл бұрын

Is there a chance you can make a dependent drop down list , in my case , with some words who contain number ? For example, product Europe , and the project to be Europe 123? I tried different options, but I didn’t solve the problem. Hope you can help

@AccessAnalytic Жыл бұрын

I don’t understand sorry, what’s the reason you can’t use the technique in the video?

@minamishail_Tech2024 10 ай бұрын

Hi, can I use Full Excel features, Power BI and other Microsoft analytics tools on MAC, or it's not supported?

@AccessAnalytic 10 ай бұрын

Hi, not supported. Elements of Power Query have been added to Mac Excel 365 but no Power Pivot and no Power BI

@patrickkinbonso1809 Жыл бұрын

Multiple observations. 1. Instead of using xlookup, can't we just use filter again?. 2. Rather than dragging the formula results down to empty rows and havin calc error, perhaps we can use the hash symbol when we reference to cell information on the other column when you have already used a dynamic array unique function? This will spill the results down as far as the first unique spill list goes and save from calc error due to empty rows cell?

@AccessAnalytic Жыл бұрын

Hi, 1. I don't think so. I don't think you can use FILTER inside data validation. XLOOKUP has a special feature in that it can return a range. 2. No, as I mentioned in the video Arrays of Arrays are not possible currently.

@patrickkinbonso1809 Жыл бұрын

@@AccessAnalytic Good shout .. 😉

@rgportocarero7889 4 ай бұрын

Dear Access Analytics, Many thanks for your video. Very elegant solution. The problem I am experiencing is that I cannot copy the Xlookup formula (Control C) and subsequently paste the formula in the drop-down list (Minute 7:21). When copying the formula or any formula from a cell, excel thinks I am editing the formula cell. On the other hand, if I directly type in the Xlookup formula in the data validation list, the list generates an error (direct formula's in data validation is not possible). Help on the matter is very much appreciated!! Regards,

@rgportocarero7889 4 ай бұрын

Hi, Referring to my previous question. 1) I had to press control C + escape to only copy the formula and not edit the cell 2) I can only make the data validation link on the same worksheet as the raw data (no link between other worksheets possible). Many thanks

@AccessAnalytic 4 ай бұрын

Yeah notice hi highlight the text from within the formula and then do Ctrl C. Make sure you’ve set up the named ranges and that your formula refers to those named ranges.

@AdelineIce 10 ай бұрын

Really great video. I love the formula. Unfortunately I found that it doesn't work well if the same value in level 2 fits more than 2 values in level 1. For example, I use company name as level 1, city as level 2 and position name as level 3. It turns out that if I select a city at level 2, the result 3 list shows me positions that don't fall in selected level 1. Is there a way to prevent this? Example of the input data table X / London / Driver X / New York / Accountant Y / London / Baker When I select company X, London, the position sheet also shows Baker, and whatever is not in the list for company Y.

@AccessAnalytic 10 ай бұрын

Sounds like the master table might not be set up correctly in that case. I’ll have a check to see if I can replicate in a few days time.

@AccessAnalytic 10 ай бұрын

Ah yeah, I've taken a look and I see what you mean. I'll have a think about and adjusted formula. The simplest instant fix is to make the values slightly different, maybe with a prefix or extra spaces or underscore

@AccessAnalytic 10 ай бұрын

I’ve found a solution, I’ll post a link to a fine once I’ve finished it

@AccessAnalytic 4 ай бұрын

Took a long time for me to get around to the update but here it is: kzfaq.info/get/bejne/eNVxp9OLrszDn40.htmlsi=ybJkE55oux97_AAW

@AdelineIce 4 ай бұрын

Thank you so much! I cant wait to see it.:) @@AccessAnalytic

@josephrichardlazaro-hs4iu 8 ай бұрын

Hi Sir, are you using online excel or 365? is it applicable to Excel 2019? Filter function is not working in my excel. please help

@AccessAnalytic 8 ай бұрын

Works in desktop 365, web, and I think Excel 2021

@claudialemelin2926 6 ай бұрын

The # of the end of the formula in validate liste did work in excel online ? @@AccessAnalytic

@ClaudeBalleux 9 ай бұрын

thank you. This is a very good way to do this. But, I wonder about the formating. When I'm adding this to the file, suddenly Excel stop to work. First it become very slow and then I have to closed it. Any cue of why Excel is reacting like this? If not, no worries, the rest of the video is already much helpfull. Thanks again.

@AccessAnalytic 9 ай бұрын

How many cells are you applying the conditional formatting to?

@ClaudeBalleux 9 ай бұрын

@@AccessAnalytic Only 60. This is weird. is'n it?

@AccessAnalytic 9 ай бұрын

@@ClaudeBalleux yeah, I just tested it on 1,000 and is fine, althrough not much else in my test workbook. If you can open in Excel for web and ty out the new Review > Check Performance button - maybe that will shed some light.

@browsingbebas4144 4 ай бұрын

It's the easiest way but still got minus in it, which the sub 'dependent col' only dependent on the 'dependent col', then we have to change the display or appeareance the entry that not exist. i was hoping for automatic drop down list which really attached to every upper dependent

@AccessAnalytic 4 ай бұрын

Yeah, I need to do a follow up video that solves that issue

@Alternativeproconcept 4 ай бұрын

Is it possible to apply in Google sheet ? Or any alternative ?

@AccessAnalytic 4 ай бұрын

I’ve never used Google sheets sorry

@user-pc8jh6rj9q 2 ай бұрын

Is there a way to replicate that simple spill function in google sheets? This is the exact solution I need but that missing part kills it for me. Any help would be appreciated, thank you!

@AccessAnalytic 2 ай бұрын

I’ve never used Google sheets sorry

@user-pc8jh6rj9q 2 ай бұрын

@@AccessAnalytic That's alright I appreciate the response and your video helped with the logic side. For those looking in the future, I was unable to replicate the # function in google sheets, I instead used =ArrayFormula(transpose(filter(AC$2:AC,$AB$2:$AB=$D2))) with AC and AB being helper columns (AB - Category | AC - Subcategory) I then used the method shown in this video to create a conditional dropdown based off of the results from the formula which span from cells AE:AU. I had to drag this formula down as far there were categories. With the categories, it's important to stick to the same structure shown in this video. If there are 15 items under one category you should have 15 lines of data - the formula relies on that. Hope this helps someone out there!

@LilLinh 2 ай бұрын

how can we do it if level 2 have same value but value 3 have different value. for example: Level 1: A, B, C, level 2: small, medium, large, and level 3: A-small, A-medium, A-large. And value for B level 3 is B-small, B-medium, B-large. As level 2 of A, B, C are the same, can we do it with unique?

@AccessAnalytic 2 ай бұрын

Check out this version: kzfaq.info/get/bejne/eNVxp9OLrszDn40.html

@Larida101 4 ай бұрын

Hi, what about very long lists that I can't add horizontal due to number of cells each?

@AccessAnalytic 4 ай бұрын

I’m not sure what you mean sorry

@Larida101 4 ай бұрын

@@AccessAnalytic it's ok. Your recommendation actually ended up working 😊 thank you

@AccessAnalytic 4 ай бұрын

@Larida101 you’re welcome

@davidabuang 9 ай бұрын

Challenge accepted ;-) I believe I have an easier and cleaner way to do a multiple, repeating drop down list that I haven’t seen demonstrated anywhere yet. How can I send it to you?

@AccessAnalytic 9 ай бұрын

info@accessanalytic.com.au 🙂

@davidabuang 9 ай бұрын

@@AccessAnalytic Workbook sent... Enjoy!