Searchable Drop Down List in Excel (Very Easy with FILTER Function)
Рет қаралды 1,955,112
Күн бұрынJoin 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover a straightforward method for creating searchable dropdown lists in Excel without using VBA. This tutorial is perfect for those looking to enhance their Excel skills and add dynamic, user-friendly elements to their spreadsheets.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/searchable-...
This technique uses data validation and (simple) Excel formulas to create a searchable data validation drop-down list. We use the Excel SEARCH function, ISNUMBER as well as the brand NEW Dynamic Array FILTER function. To make sure our searchable data validation drop-down list is sorted, we use the new dynamic array SORT function.
In this video, you'll learn:
▪️ Setting Up Searchable Dropdowns: Step-by-step guidance on creating dynamic and searchable dropdown lists using Excel's powerful features.
▪️ Utilizing Excel's Dynamic Arrays: Learn how to leverage Excel's new functions to make your dropdown lists responsive and efficient.
▪️ Enhancing Data Validation: Tips on improving your data validation process for a smoother user experience.
▪️ Incorporating Excel Functions: Insight into using Excel functions like FILTER, SORT, and UNIQUE to create more functional dropdowns.
We also use the FILTER functions instead of VLOOKUP or INDEX & MATCH to find the value that matches our selection.
00:00 Searchable Data Validation in Excel without VBA
01:11 Searchable Drop-Down List with Excel Dynamic Arrays
02:53 Excel SEARCH function
04:56 Excel ISNUMBER function
05:18 Excel FILTER function
06:42 Excel UNIQUE function
07:05 Excel SORT function
07:16 Create Data validation list with Excel Spill Array
08:26 Excel FILTER function for Lookup
09:28 Using Official Excel Tables
LINKS to related videos:
Dynamic arrays announcement: • Excel Dynamic Arrays (...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
@LeilaGharani 4 жыл бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/searchable-dropdown-file Update 2023: In Office 365, data validation lists are now searchable by DEFAULT! Check out this video for more info: kzfaq.info/get/bejne/abeYaNGG0qm6lGw.html For Searchable Drop-Downs on MULTIPLE rows check out this video: kzfaq.info/get/bejne/rcehrdp-qL-oaX0.html
@deepjyotistories 7 ай бұрын
Thanks for editing this comment. After trying this out last night, I was confused as they are already searchable.
@karthikbhupathy4598 5 жыл бұрын
Content - 10/10 Way of Explaining - 10/10 Your knowledge and teaching skills are extraordinary!!
@niyanaknowledgecave3277 5 жыл бұрын
Fully agreed!!
@smktllc730 5 жыл бұрын
filter arry is not showing in my formula ,please help
@Usman-ml4ig 5 жыл бұрын
And your buttering- 11/10
@ollieegdk 5 жыл бұрын
Annnd i just subbed
@OmarFeliciano 4 жыл бұрын
Man something similar I posted a few minutes ago, she show/teach us in a clean, fast and to the point manner that I just love it. She became one of my favorites at first sight.
@Avi8053 4 жыл бұрын
You are brilliant, even after years of using Excel I always learn a few things from your videos.
@stuartkay1393 4 жыл бұрын
Excellent! First I've been looking for a way to allow users of my sheets to narrow the DV list based on letter searches, without VBA. So thank you. Secondly, I didn't know about the new 'Sort', 'Filter' and 'Unique' functions. These will save me a lot of time using permutations of INDEX MATCH COUNTIF etc. You've added value to my work, thank you!
@simm1872 2 жыл бұрын
Excellent illustration dear Leila Gharani ! Effective useful for the IT development for the human society ! Appreciate your great services in these training videos ! We wish you and your family / team all the best of time, health and peaceful happiness !
@woutert114 5 жыл бұрын
This looks awesome. I've made a searchable dropdown before but it was way complicated and required like 4 extra column before it would work. Lots of data that had to be hidden on the same worksheet and that could be messed up by inexperienced users. Dynamic Arrays and the Filter function sound like the late Christmas present we didn't deserve but desperately need. Great video!
@gottahaveit3774 5 жыл бұрын
.. and this is the comment that helped me to figure it out on 2013. Thank you wouter114!
@alikadim6908 3 жыл бұрын
I had watched office tutorials since years, and without any doubt you are the best
@steves6557 3 жыл бұрын
I used to do something similar with those awkward Combo Boxes. Liela's method is superior and has the added plus of auto-expansion with additional rows of data. Invested 11 minutes into the video, followed by 5 minutes applying my new found knowledge and 16 minutes later, my colleagues were ready to run with a new and much improved model. 🙌 Thank you!!! 👍
@longbill5429 3 жыл бұрын
Thank you so much! beautiful Leila. I am using excel a lot, but still learn new thing from you. Love your voice and your way of teaching. You rock!!!
@HectorMtnz 3 жыл бұрын
Totally mind-blowing that this material is free of charge. Great content, keep it up! :)
@dannyzhu7256 5 жыл бұрын
Great video. The question I have is that if I want to create a dynamic drop down for multiple cells?
@saschakarbacher5867 2 жыл бұрын
Hi Leila. Thank you so much for your videos! I‘ve started working on a little company and they have NO tools for analysing their business. So I have to create Tools for Monitoring, vacation planning and much more. With your Videos, I can create and finalise my Ideas. Thank you for your overall good work!
@stefanogattoCH 3 жыл бұрын
Thank you very much Leila! Your presentation allow me to keep up with Excel new stuff, which goes quickier than in the past i feel!
@DebashisTalukdar 4 жыл бұрын
Leila, Thank you so much for sharing so many of these Excel tips and tricks. I'm an advanced Excel user with over 20 years of Excel Development behind me, and I find that a lot of these new features that you share are immensely useful! Thanks so much for putting these videos out there in an easy to follow and understand format! You are AWESOME!
@LeilaGharani 4 жыл бұрын
Many thanks for the kind feedback. It's great to have you here :)
@abrotherinchrist 5 жыл бұрын
Best Excel channel out there. I like that you are always on the cutting edge of new features. I've been using Gsheets lately as well and I really like that you can select entire ranges from one cell onward like this: A2:A (which selects A2, A3, A4...). I wish Excel had that without a lot of complication.
@LeilaGharani 5 жыл бұрын
Thank you George!
@uglyburrito_ 4 жыл бұрын
It's very satisfying watching someone know what they're doing, thank you !
@michaelsvenson2456 Жыл бұрын
Just got myself into more advanced Excel and this is amazing, easy to understand and full of tips that are usefull, perfect.... Also "your" VBA is great 🙂
@nmbspltd5766 Жыл бұрын
This channel is superb. Would recommend it to all excel users.
@OzduSoleilDATA 5 жыл бұрын
BRILLIANT! Great solution to a long-time need! I've had to do this in the past and it was a crazy mess.
@LeilaGharani 5 жыл бұрын
Thank you! Yes - agree - it was a crazy mess before :)
@musthaktirur5218 3 жыл бұрын
Thank you
@g4gypsy 3 жыл бұрын
Your videos are amazing and easy to follow. I was able to create reports following your instructions. Thank you so much!
@robertcortes2315 3 жыл бұрын
im just new in these application and found these 11/10 tutorial, thank you for this Ms. Leila.
@ReeshaYaser13 5 жыл бұрын
The technique and the way of delivering, I love it.
@chrisk.9765 2 жыл бұрын
ARRAY functions, currently one of the best functions in Excel which are saving a lot of time. My next wish is to have more advanced conditional formatting (e.g. the line thickness depends on cell value, becauseat the moment it is possible to set only the standard thickness line) It will be helpful and will avoid additional work with VBA just to set specific/more advanced conditional formating. Perhaps M$ will consider that feature in some update...
@Alejandro-db2xj 2 жыл бұрын
Empeze viendo un video especifico y termine encontrando un canal con excelente contenido y perfección al explicar! Muchas gracias
@Xtrvler Жыл бұрын
This video is great! I've seen a cpl different ways to do this with none of them as concise and easy. Even 4 years later, yours is the best! You got a new follower.
@yusof-denjamasali2172 Жыл бұрын
I really like your videos. The pace can easily be followed, the video editing is excellent, your voice is so calming and you are beautiful. Thumps up. Thank you for sharing your knowledge, ma'am. God bless you.
@andread2138 5 жыл бұрын
Best video for searchable drop down list!Awesome!!!
@ronykeya3530 2 жыл бұрын
Hello Leila, your content is always helpful and insightful. Wish you knew how much you've helped me improve my excel skills. Thank you
@yogitseth8693 10 ай бұрын
Most of your Excel videos are worth the time, your teaching skills are 10 / 10.
@osamudianmenigbinovia 4 жыл бұрын
I bless God for the day i found you Leila. Amazing tutorials.
@LeilaGharani 4 жыл бұрын
Thank you for the kind words!
@alexgranowsky2102 4 жыл бұрын
Osamudianmen Igbinovia hope she see this brogod 💯💯
@debbiedaly1809 2 жыл бұрын
Ditto
@1gopalakrishnarao 5 жыл бұрын
Thanks once again to my Beautiful/Brilliant/Brainy/witty Excel Teacher. Every Thursday, I am waiting to see your videos eagerly. Really You are doing a great Job for the Excel lovers/users. Thanks a lot . You are a shining/Twinkling star in the Excel Universe.
@LeilaGharani 5 жыл бұрын
Thank you very much Gopala for your kind words and your support. I'm very grateful for the Excel universe.
@1gopalakrishnarao 5 жыл бұрын
@@LeilaGharani Great Salutations once again to my Excel Guru. Thank you
@romitagarwal572 2 жыл бұрын
Your is the best excel classes on KZfaq i have seen your method of explaining complex formulas is amazing and easy to follows thanks a ton for posting and sharing your experience and knowledge its very beneficial to people like me
@shawnw.4440 3 жыл бұрын
Thank you so much for this video!! I own an auto mechanic shop where each tool is entered to inventory by the 'office' people but the mechanics need to quickly search if we have a tool and where it is located. I entered the formulas into my spreadsheet in one window, right along with this video in another window, and published it to the shop's desktop at the end of the video. 11 minutes invested watching this video will save my staff hours of having to search the tool room to determine if we have a specific tool and if yes, where it is.
@LeilaGharani 3 жыл бұрын
That's a perfect use Shawn. It's great that you were able to implement this for your purpose.
@MsRainbow1983 4 жыл бұрын
You are incredible - I am learning so much from you. Easy to follow, enjoyable - thanks so much!
@desotronics 5 жыл бұрын
That's super cool ! i can make the telephone list fancy in my company :) Thanks keep staying awesome Leila!
@boele0707 3 жыл бұрын
I see a huge value in this!! Big fan of your videos Leila! You have no idea how much you have influenced the way I am using excel nowadays. Big promotor of your videos. Often promoting them to my team all over the world. Thank you so much!
@LeilaGharani 3 жыл бұрын
Many thanks for your support!
@ajitjoshi4968 Жыл бұрын
I am very thrilled by the searchable drop down list. I tried using the method and It worked. I also saw the link in the comment below to extrapolate the list to other rows. Thanks a lot..
@blue-oceandreamer2756 2 жыл бұрын
This 2019 video production shows what a Excel master Leila has been while compared with another "cookie" video produced by someone else 2 years later. To surpass Leila's originality, creativity and well thought layout and illustration has never been an easy task. Leila, I love your tutorial and teaching talent so much. Time's well spent. Thank you.:)
@LeilaGharani 2 жыл бұрын
Thank you very much for the kind feedback!
@777ericghim 3 жыл бұрын
Hello! Very helpful content, thank you very much. I am trying to have multiple of the searchable drop down list on the same column. Does anyone know how to do so? I tried including the whole column in the search argument but it doesn't work. Thank you!
@AlexandreGentilSousa 4 жыл бұрын
Definetely, Leila is the best Excel teacher ever ! I´ve been browsing thought many channels looking for soluttions to a new Excel system I have developed to a small business and most of the time is Leila that has saved me. All explanations are very didactic, she is very sweet and we always get other knowledges together. Many, many, many thanks !
@LeilaGharani 4 жыл бұрын
Thank you very much for the kind feedback Alexandre!
@dimitrz2000 3 жыл бұрын
I follow your videos they make complex things in excel so simple. Thanks 👍🏻
@maeyks 4 жыл бұрын
Good morning leila, is there a way that this feature can be done, on every line item or row in excel? i find your videos very easy to follow, that's why i subscribed to your channel. thanks!
@gabrielespinosadasilva527 3 жыл бұрын
Thank you very much. The way you teach combine many contents(formulas) to solve one problem is very good.
@KarolJankowski 3 жыл бұрын
AWESOME, I've been looking this in years by now!
@gajanandhumal8883 5 жыл бұрын
Thank you very much..solved my problem as i always wanted searchable drop-down list like Google sheets in excel.
@yahtadi5152 4 жыл бұрын
How to make searchable drop-down on Google sheets?
@MalinBHolm 4 жыл бұрын
As a few others who have mentioned this, I needed a searchable dropdown list that could work for multiple rows. I found a solution that worked, however it is probably not very elegant and I would welcome some other suggestions! I needed to do this without VBA as I needed to share it with someone outside my organisation. Note, that this only works if you are filling out a list that is always going down. 1. Column A in sheet "Data capture" is the column I want the validation list to display in. 2. Create a dynamic selection using name manager to select all cells with a value in: Name: nm_meter_input, Refers to: =OFFSET('Data capture'!$A$2,0,0,COUNTA('Data capture'!$A:$A),1) 3. Create the formula in a reference cell (not to be seen or used by anyone, only by another formula), in this example I have entered below formula in cell I2: =IF(COUNTA(nm_meter_input)=0, "A2", IF(COUNTIF(tbl_meters[Name], INDIRECT("A"&COUNTA(nm_meter_input)+1))=0, "A"&COUNTA(nm_meter_input)+1, "A"&COUNTA(nm_meter_input)+2)) 4. Enter the below formula in another cell, in my example I have entered in K2 =INDIRECT(I2) 5. Then reference to the cell K2 in the Filter formula =FILTER(tbl_meters[Name], ISNUMBER(SEARCH('Data capture'!K2, tbl_meters[Name])), "not found")
@mihaelablagova3300 2 жыл бұрын
I needed searchable drop-down in a table column with many rows. I used the solution of Leila, wrapped in TRANSPOSE function. This way in my helper sheet i didn`t get the #SPILL error cause the results were passed to the row cells. Then in the data validation I referred to the helper sheet using the #. I had concerns it won`t work because the results of the search are not spilled in a helper column but in rows. But it worked :). Not sure if my explanation is good, but I can send you some sample sheet if you need.
@LiLMARSLI 2 жыл бұрын
@@mihaelablagova3300 That one worked but I couldn't figure out how to copy&paste the cells with "drop-down list" and keep them linked to their own correspondent cells in the "search" column. The list I am preparing can be 10 lines or 100 lines, who knows. I can't do it one by one :(
@Alfactube Жыл бұрын
after watching many of your and others videos, your the best, clear, simple and thorough, becoming a fan....
@CarlosMorales-ng5wu 4 жыл бұрын
I am glad I found this video, the way you explain it is fantastic. Thank you so much for this Leila and I will definitely sign up for the courses... your teaching skills are amazing!!!!
@LeilaGharani 4 жыл бұрын
My pleasure Carlos. I hope you'll like the courses.
@khaledenbaya6446 5 жыл бұрын
I was wondering if I could use "Goal Seek" as dynamic function to repeat it in each cell.... Thank you
@mpattym 5 жыл бұрын
For those who are trying to get version 1904 for the new features you need to change your office level to "Insider" (just above the update button). This gives you access to pre-releases, be aware these can be buggy.
@chatureshpatel1159 7 ай бұрын
Never have I watched an Excel training this long. Watched at 2x speed. Absolutely full of gems. Really well done -/ thank you!!
@LeilaGharani 7 ай бұрын
Great to hear!
@maheshpatil2017 4 жыл бұрын
I have referred lots of youtube channel for excel but you are cut above all channel. you are leading excel teacher on youtube.
@ramkrishnashiromani78 5 жыл бұрын
ma'am can u show how to prepare school time table automatically .....
@delsam1215 5 жыл бұрын
Thank You Leila, another question what formula's could one use to get the same results that you're demonstrating
@maxwellmbulo1226 2 жыл бұрын
Mywy
@maxwellmbulo1226 2 жыл бұрын
Hng y
@nonoobott8602 2 жыл бұрын
The way you explain very clearly is amazing. Learned so much from your Channel. Thanks for sharing
@andrewmuzava2156 4 жыл бұрын
You are simply one of the best, thank you!
@eams4647 4 жыл бұрын
Hi, appreciate ur great efforts on explaining new things to us. My question: Incase of "Not available" in your example - is there any possible way out to update the list at the dropdown area.?
@nsmust1996 3 жыл бұрын
+1
@jouckvanwageningen7939 3 жыл бұрын
I gues you could with vba, using the change event. When cellvalue = Not found add text to table. Something like that. Im gonna try to make this work.
@basengelblik5199 4 жыл бұрын
Hi Leila, Great function, However I get an error message when adding the "FILTER" part.
@ImranKhan-vb7ke 3 жыл бұрын
just check your Excell, go to FILE - ACCOUNT, check which office is showing, sometimes its shows previous version of offices, just click update button to update your office 365.
@UmarKhan-qx7fc 3 жыл бұрын
Did you solve this??
@JeffTurner15678 2 жыл бұрын
You provide outstanding tutorials for Microsoft Excel, outstanding job on creating and providing very useful training content. I am currently on a mission to update and refresh my Excel and Access skills, I am finding your content to be top notch!
@bisikolok 4 жыл бұрын
i've learned millions of new things in this video. especially using table feature at the end blew my mind! thanks a lot!
@LeilaGharani 4 жыл бұрын
My pleasure. I'm glad it's helpful.
@aldinededel8619 4 жыл бұрын
Great video, learned a lot. I have a question, how this data validation can be replicate in other rows? thanks
@debjitpaul3399 3 жыл бұрын
kzfaq.info/get/bejne/rcehrdp-qL-oaX0.html
@suedavis1337 3 жыл бұрын
@@debjitpaul3399 Thank you!
@alanchai76 4 жыл бұрын
Hi Leila, would you mind to show us how to deal with multiple cells in a column using this searchable drop-down list?
@SaddamKhan-jd2sj 2 жыл бұрын
At 6:28, she removed the D1 from the formula bar, then she selected B5 on the other sheet. I suppose instead of B5, you'll need to select your required cell range at this step. I'm not sure though because I don't have O365 to test this
@SYEDMOHAMMMADKASHIF 2 жыл бұрын
@@SaddamKhan-jd2sj It is not working in that way.
@SaddamKhan-jd2sj 2 жыл бұрын
@@SYEDMOHAMMMADKASHIF do you know the answer?? I'll have to practice this tomorrow and try to find an answer.
@AndresGonzalez-ok2jx 2 жыл бұрын
Please help Leila! :(
@SaddamKhan-jd2sj 2 жыл бұрын
@@SYEDMOHAMMMADKASHIF there is already a video by Leila, just search: Many Searchable drop-down lists
@sjh1956 4 жыл бұрын
Hi! I just love your tutorials! However I have a somewhat different approach to this example on dependent drop-down lists. First of all, I “always” work with named ranges (even in tables). So; I renamed the Customer column CustomerData, and inserted a second column with the first and last names switched (using flash fill) named Customer. Then I used this formula in the validation prep: =UNIQUE(SORT(FILTER(CustomerData;LEFT(Customer;LEN(Report!B5))=Report!B5;""))) This means that you search (part of) the customers last name, but when you press the drop-down button the list will be shown as first name - last name. You could of course omit that extra column, but then this formula will only filter on first names. And, if you leave out the LEFT and LEN functions, you would have to input the full name in the drop-down box for this to work (rather counter-productive in my opinion).
@seb6351 3 жыл бұрын
Amazing, very well explained and simple ! Thank you
@gerardvaneggermond9967 4 жыл бұрын
Hello, I have watched your video on another computer, I am working with Office 365 yet your formula does not work for me, I do not have a filter, it already starts searching I get another error message, despite the correct forum formula, also want I have it in the drop down list, can you help me?
@McKaySavage 5 жыл бұрын
Hi Leila, this is a fantastic addition to the drop-down techniques! I have a question though: if one wants to implement this say in a form where there is 3 columns of dynamic dependent drop-downs and many rows to fill in, the next challenge to solve is how to make each searchable without needing to set up a specific array column for each. I use this scenario a lot with NGOs where one might be selecting geographic info, eg State/Province -> District -> Town/Village. With up to 50 rows per form, that would be 150 filter columns to set up with this method! Any ideas of how to solve this (at least until Microsoft allows the array functions inside the validation formulas)?
@GrishaNiwa 2 жыл бұрын
Hi McKay, did you solve your problem? I am having same issue and looking to solve it as well..
@McKaySavage 2 жыл бұрын
@@GrishaNiwa No, unfortunately I haven't seen or figured out any straight-forward method that doesn't require tons and tons of hidden back-end columns, with all the fragility and calculation cost that requires. To be honest, I've just started pushing Google Sheets for more projects involving data entry because the auto-fill of data validation is just such a better experience.
@GrishaNiwa 2 жыл бұрын
@@McKaySavage Thank you for your feedback. At last i have moved forward with the tons and tons of hidden back-end columns...:) There are more options if you want to consider VBA .. but I disregarded this approach.
@sarahogan1715 2 жыл бұрын
@@McKaySavage Have you considered using Slicers?
@McKaySavage 2 жыл бұрын
@@sarahogan1715 how would slicers help with an input form with multiple rows per form?
@SBFay01 3 жыл бұрын
Great tutorial. So glad to have found your channel today!!! I didn't realize it, but I think my Excel knowledge froze and stopped making new progress about 10 years ago. Learning so many new things from you. And I've only watched 3 of your videos, so far! Thank you.
@LeilaGharani 3 жыл бұрын
Glad to have you aboard :)
@xanglz1785 3 жыл бұрын
Wowww, I'm just so amazed by the teaching skills of you, Leila Jan. So concise, smooth, simple, and well-designed. And I'm not talking about this tutorial only; I've watched more than almost 10 videos, so far and I can't stop myself continuing. Just great JOBBB!!!
@LeilaGharani 3 жыл бұрын
Wow, thank you!
@michaelp.caputo8190 3 жыл бұрын
Great tutorial. I have a quick question. What if you want to use the same lookup targets on multiple cells. I have a test script and each row has an assignee. I want to use the filtered lookup for each row based on the same list of global testers. If I copy the validation down, the filter is locked to the first cell.
@Bearded_Misio Жыл бұрын
Got the same issue, we are keeping a tracker of input based on the same input possibilities with over 1k rows to choose from so It would be great to have the function work on multuple rows without duplicating the drop down data sheet.
@angeloaquino1121 Жыл бұрын
remove the f4 function for cell in the validation
@priyankamulay9140 Жыл бұрын
Hello Angelo can you please explain bit more . How can we achieve this for whole column
@priyankamulay9140 Жыл бұрын
@@angeloaquino1121 hello can you please explain bit more that how can we achieve this for whole column?
@0LMG Жыл бұрын
@@priyankamulay9140 lo que él quiere decir es es que la función f4 va a convertirla en absoluto y si quieres también puedes desactivarla de la misma manera. Presiona ctrl+f4
@mattrudd1411 4 жыл бұрын
This works good, however is there a way when I start typing into the cell box lets say "gar" it will pop up the choices without having to click the down arrow?
@realfrancisco 4 жыл бұрын
right around min 25 here kzfaq.info/get/bejne/qdiEp8iK1NfHeqc.html
@marcoleao2409 4 жыл бұрын
Hello Leila, you are awesome! I have learned a lot from you. I have a question: what if I have more than one droplist, what does the filter look like? I say in this same example of yours if there are 2 or more droplists in a row.
@ptoricoluv 2 жыл бұрын
Thanks! I love the way you explained everything. So easy to understand and follow. Wish I could give more but I hope others give and collectively it all compensates for the amazing work you do in sharing your knowledge. Thanks again.
@LeilaGharani 2 жыл бұрын
Thank you very much!
@hamlinhamlinmcgill630 5 жыл бұрын
Is there a posibility for auto-fill-in at the moment when you begin typing?
@LeilaGharani 5 жыл бұрын
My favorite show :) Not with Excel formulas - Excel calculations run after we click - this gives the data prep list the opportunity to update...
@belenbaixauli5218 2 жыл бұрын
@@LeilaGharani Could you teach us how to do it, if possible? Perhaps with VBA? Thank you very much in advance, super grateful for all your tutorials
@sassy2721 2 жыл бұрын
Hi, thank you for sharing this function. May I ask what if you want to use that dropdown function in every row, how do you make the "search cell" dynamic following the cell?
@wesverg658 2 жыл бұрын
same question here..
@rachmadhusnianto3506 2 жыл бұрын
@@wesverg658 is there an answer for this quest ? i have the same and need feedback.
@rachmadhusnianto3506 2 жыл бұрын
is there an answer for this quest ? i have the same and need feedback. many thanks
@urvashichauhan9397 2 жыл бұрын
Have you get the solution of this?
@tsamichuzyme2658 Жыл бұрын
@@rachmadhusnianto3506 - Found another tutorial that helped me to create a searchable and dynamic dropdown function. kzfaq.info/get/bejne/nNl8aJqT28qbgJ8.html
@HM3960 8 ай бұрын
Perfect Tutorial. Thank you
@andre-tt3pp 4 жыл бұрын
Didatics nothing less than amazing. Thank you!
@kirtanpandya 4 жыл бұрын
Leila, This is very good content. However I have question, what if I have multiple drop-down cell or sheet where I want to use same master data list with Searchable Drop Down List..Is this doable?
@Asbjoern 4 жыл бұрын
You would need multiple search tables. The master data remains constant.
@ianbarnette2071 3 жыл бұрын
I love this! Thank you. How do I continue this drop-down in a column rather than just one cell, e.g. A2 is where my Drop-Down is now but I need to continue all the way to A1000?
@rcsamaral 3 жыл бұрын
did you solve this problem? i have the same question?
@ianbarnette2071 3 жыл бұрын
@@rcsamaral yes, she has the video on her channel that focuses on this question.
@christinavaughan6178 3 жыл бұрын
@@ianbarnette2071 which Video is that? I have the same issue
@ianbarnette2071 3 жыл бұрын
kzfaq.info/get/bejne/bdOfZ6aA082bg5s.html
@christinavaughan6178 3 жыл бұрын
@@ianbarnette2071 Thanks heaps for your replay but I'm super sorry but I'm not sure how this solves the issue with searchable dropdown lists? Has anyone got it working? Thank you
@RedaAhmed 2 жыл бұрын
Saved my time! i was rushing to finish something so i just copy the formula you typed xD. thank you so much Leila
@varvarapapadopoulou9875 4 жыл бұрын
I don't have Filter & Sort function in my excel, can u advise me how to install it? I use office 2016
@q-breezy4826 4 жыл бұрын
$ office 365 $
@mmmmm 4 жыл бұрын
This only applies to a single cell search, is it possible to extend the technique to multiple rows/cells?
@lanyburnett7966 4 жыл бұрын
I tried this and you need to create a new list for each cell you want to be searchable.
@VinnyMagoo2 2 жыл бұрын
That was explained so perfectly! Thank you!
@pushpajchougule8316 4 жыл бұрын
Hi Leila - Great video as always. Thanks much. Q: How can i make it display the applicable names automatically instead of typing keywords & going through dropdown?
@rajeshveljishah1299 4 жыл бұрын
how can i have multiple searchable drop down list ? for example i have 50 rows, every row i need to have searchable dropdownlist, where the data is common. with single data preparation
@hex-2748 4 жыл бұрын
Copy that cell and paste special. Validation.
@rajeshshah6758 4 жыл бұрын
@@hex-2748 done
@lordstevent9030 3 жыл бұрын
@@hex-2748 That works for the normal data validation but I don't see how that would work here since the validation array is now looking at only a single cell to populate the list.
@ananbalakrishnan6837 3 жыл бұрын
@@lordstevent9030 she has an updated video...Many SEARCHABLE Drop-Down Lists in Excel (No VBA)
@swakumar1227 3 жыл бұрын
It's working for only one cell I need similar data validation for A1:A10 cells please advise.
@lordstevent9030 3 жыл бұрын
I need a solution for this also.
@ananbalakrishnan6837 3 жыл бұрын
@@lordstevent9030 Yes, can it work for more than one cell? I was hoping it would until i tried it
@muhammadmaazejaz8621 5 ай бұрын
Thank you so much for sharing so many of these Excel tips and tricks
@jessicathomas73 3 жыл бұрын
This is truly brilliant - thank you!
@andrefilgueiras 5 жыл бұрын
So Filter function will kill VLookUP, very nice!
@LeilaGharani 5 жыл бұрын
Mostly yes - unless you just want the first match or you just have one match and you don‘t want your results to spill.
@andrefilgueiras 5 жыл бұрын
Thank you very much for your repply Leila! I admire your work.
@youguesswho26 5 жыл бұрын
what if you wanted to implement this on a set of data on a row by row basis? For example if I had the first drop down list in A2 and wanted the second drop down list in B2. And then A3 would limit B3 and so on. I can't seem to solve that issue...
@jorgemacgenity1367 4 жыл бұрын
Hello Chuz Lahoria, take a look at my answer. I hope this help you.
@kirtanpandya 4 жыл бұрын
@@jorgemacgenity1367 Don't see your reply... "I have question, what if I have multiple drop-down cell or sheet where I want to use same master data list with Searchable Drop Down List..Is this doable?"
@jorgemacgenity1367 4 жыл бұрын
@@kirtanpandya Here is the link to my topic: kzfaq.info/love/Ao3Xf4NBuQxuSI1K7kADiw
@mdobejr 4 жыл бұрын
@@jorgemacgenity1367 your link takes us to a blank, page, but I'm interested in this answer!
@yououtuber4176 4 жыл бұрын
@@mdobejr That is his answer. Nothing. He doesn't know either. I tried to do this a few years back with index/match, but the data got too much for Excel to handle and it slowed down terribly until the workbook crashed. If this has been addressed, I would be very interested to know too!
@petlover6072 8 ай бұрын
Thanks a lot. Your videos are real helpful.
@LuisGarcia-mn4jg 2 жыл бұрын
I don't know how you know the next questions and things that I want to do in excel. Genius! Thanks so much, I promote your channel as much as I can as I find it very useful and easy to understand.
@LeilaGharani 2 жыл бұрын
Wow, thanks Luis!
@nktamrakar 5 жыл бұрын
Filter function doesn’t seems to be available for general public. How can we add this on our excel?
@kim13sep 5 жыл бұрын
I think you need to have Office 365 installed.. I have Office 2016 and do not have the FILTER and SORT functions available.
@bluceree7312 4 жыл бұрын
You can use a simple IF function.
@PaulDewseth 4 жыл бұрын
@@bluceree7312 how?
@bluceree7312 4 жыл бұрын
@@PaulDewseth Its been a long time can't remember but I think its actually not possible using IF.
@PaulDewseth 4 жыл бұрын
@@bluceree7312 its fine, i found a solution for my current problem. thanks buddy
@mundonemesis 3 жыл бұрын
04/2021 and you still taking me to school.Thank you Leila
@thefencetheend616 4 жыл бұрын
Great lesson, easy and simple!
@mohobbatjsr 4 жыл бұрын
I use excel 2007, here "filter () Dynamic array function" is absent so how can I use this formula? If have any VBA system please can you give me the link so it is very Helpful to me.
@StageMasti 3 жыл бұрын
kzfaq.info/get/bejne/e7F7ea-lmq_Ye5c.html
@perlaparedes7911 4 жыл бұрын
I keep getting the following message when I include the Filter formula "That function isn't valid" I am using OFFICE 354 MSO? Do I need to use a different formula?
@abdullahdemir1243 3 жыл бұрын
I've got the same problem, please help us....
@ImranKhan-vb7ke 3 жыл бұрын
@@abdullahdemir1243 just check your Excell, go to FILE - ACCOUNT, check which office is showing, sometimes its shows previous version of offices, just click update button to update your office 365.
@felixt7 3 жыл бұрын
@@ImranKhan-vb7ke hi Imran, I have same issue with excel 2016, it available FilterXml; filter function is only for 365?, thanks.
@UmarKhan-qx7fc 3 жыл бұрын
Did you fix this?
@yunisahmed4772 Жыл бұрын
Definitely, you are the best Excel teacher ever !
@MazharAltaf 3 жыл бұрын
Great tutorial. There is always something new to learn in your every video.
@jyllyff 5 жыл бұрын
I don't have Filter formula in my excel, what is the alternate formula ?
@TheStenius 5 жыл бұрын
With a combination of INDEX, AGGREGATE(15;6;Row()-(row of cell-1);...). It´s not a real matrix-formula, but you can just pull it down as well, because the value "ROW()-(row of cell-1)" will always increase by one for every following row. The 15 in AGGREGATE is for SMALL() and the 6 for ignoring false values, which is the major trick in that case. You just have to build your formula that way, that all your hits get the same value, typicaly just TRUE. It´s hard to explain, the exact formula depends on your specific needs... But maybe you will find something on youtube or somewhere else if you search for that combination of functions...
@JayGeneralexceldaddy 4 жыл бұрын
kzfaq.info/get/bejne/mZZzdM-p1MWmgp8.html
@hackyourself8263 4 жыл бұрын
The function FILTER() isn't working in my version of Excel 2016. Any suggestions please?
@LeilaGharani 4 жыл бұрын
The new formulas will not come to Excel 2016, only Office 365. See 1:15
@princegaurea70 4 жыл бұрын
Is there any another option?
@princegaurea70 4 жыл бұрын
@Life Goes k, thanks for explanation.
@Hey_Delight 4 жыл бұрын
Watch Neil Firth Searchable Dropdown list...if you"ll be using one cell dropdown ..for multiple cells, others use =cell("contents"), but this applies to all cells in a workbook.
@jianhuitan4663 3 жыл бұрын
kzfaq.info/get/bejne/nKpyZK6l3s7UY40.html this can act close to a filter function!
Leila Gharani
Рет қаралды 240 М.
MH Tech
Рет қаралды 1
Chandoo
Рет қаралды 43 М.
The Office Lab
Рет қаралды 515 М.
MyOnlineTrainingHub
Рет қаралды 127 М.
Access Analytic
Рет қаралды 55 М.