Searchable Drop Down List in Excel (Very Easy with FILTER Function)

Подписаться 2,6 млн

Просмотров 2 млн

50% 1

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

Опубликовано:

1 июн 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 2,2 тыс.

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-3Qh5nUmCVd8.html For Searchable Drop-Downs on MULTIPLE rows check out this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-waqzwMCYD9I.html

@deepjyotistories 8 месяцев назад

Thanks for editing this comment. After trying this out last night, I was confused as they are already searchable.

@Avi8053 4 года назад

You are brilliant, even after years of using Excel I always learn a few things from your videos.

@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 5 лет назад

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.

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

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

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

@dannyzhu7256 5 лет назад

Great video. The question I have is that if I want to create a dynamic drop down for multiple cells?

@shivambhola373 12 дней назад

I have also same query that this method does not work if e want to drag and drop same data validation to multiple cells

@mohammadmirzayi7572 3 года назад

عالی بود. ساده، روان، حرفه‌ای. اینطوری درس دادن از عهده هرکسی بر نمیاد.

@HectorMtnz 3 года назад

Totally mind-blowing that this material is free of charge. Great content, keep it up! :)

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

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

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

@alikadim6908 3 года назад

I had watched office tutorials since years, and without any doubt you are the best

@osamudianmenigbinovia 5 лет назад

I bless God for the day i found you Leila. Amazing tutorials.

@LeilaGharani 5 лет назад

Thank you for the kind words!

@alexgranowsky2102 5 лет назад

Osamudianmen Igbinovia hope she see this brogod 💯💯

@debbiedaly1809 2 года назад

Ditto

@uglyburrito_ 4 года назад

It's very satisfying watching someone know what they're doing, thank you !

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

@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

@nmbspltd5766 Год назад

This channel is superb. Would recommend it to all excel users.

@KarolJankowski 3 года назад

AWESOME, I've been looking this in years by now!

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

@andread2138 5 лет назад

Best video for searchable drop down list!Awesome!!!

@ReeshaYaser13 5 лет назад

The technique and the way of delivering, I love it.

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

@g4gypsy 3 года назад

Your videos are amazing and easy to follow. I was able to create reports following your instructions. Thank you so much!

@MsRainbow1983 4 года назад

You are incredible - I am learning so much from you. Easy to follow, enjoyable - thanks so much!

@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

@farhadfaraji 2 года назад

درود بر شما سرکار خانم قرنی .بسیار خوشحال هستم که کانال آموزشی شما رو پیدا کرده ام. برای شما ارزوی تندرستی و بعد موفقیت بیش از پیش دارم.

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

@chrisk.9765 3 года назад

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

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

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

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

@desotronics 5 лет назад

That's super cool ! i can make the telephone list fancy in my company :) Thanks keep staying awesome Leila!

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

@yogitseth8693 11 месяцев назад

Most of your Excel videos are worth the time, your teaching skills are 10 / 10.

@dimitrz2000 3 года назад

I follow your videos they make complex things in excel so simple. Thanks 👍🏻

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

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

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

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

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

@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

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

@seb6351 3 года назад

Amazing, very well explained and simple ! 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 года назад

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

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

@VinnyMagoo2 2 года назад

That was explained so perfectly! Thank you!

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

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

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

@xyclos 2 года назад

Fantastic leila...Love new approach of Filter function!!!

@alonsozuniga101 3 года назад

Great video, very clear and easy to follow. Thanks!

@aldinededel8619 4 года назад

Great video, learned a lot. I have a question, how this data validation can be replicate in other rows? thanks

@debjitpaul3399 4 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-waqzwMCYD9I.html

@suedavis1337 3 года назад

@@debjitpaul3399 Thank you!

@andrewmuzava2156 4 года назад

You are simply one of the best, thank you!

@muhammadmaazejaz8621 6 месяцев назад

Thank you so much for sharing so many of these Excel tips and tricks

@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

@khaledenbaya6446 5 лет назад

I was wondering if I could use "Goal Seek" as dynamic function to repeat it in each cell.... Thank you

@shawnw.4440 4 года назад

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 4 года назад

That's a perfect use Shawn. It's great that you were able to implement this for your purpose.

@davideee00 3 месяца назад

Amazing. Lifesaver, no words

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

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-srTteYoqcJs.html

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

@natcain 3 месяца назад

Very useful, just what I was looking for.

@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

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

@seyedmohamadmadahzadeh9195 7 месяцев назад

I would like to express my gratitude for the outstanding tutorial you shared.

@LeilaGharani 7 месяцев назад

You're very welcome!

@thefencetheend616 4 года назад

Great lesson, easy and simple!

@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fsL57bvd7Pk.html

@ramkrishnashiromani78 5 лет назад

ma'am can u show how to prepare school time table automatically .....

@yunisahmed4772 Год назад

Definitely, you are the best Excel teacher ever !

@Cherisse724 2 года назад

Super helpful. This is exactly what I was looking for!

@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 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-7mo4COng7Sg.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

@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

@mrmuj4039 3 года назад

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

@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: ru-vid.com/show-UCAo3Xf4NBuQxuSI1K7kADiw

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

@milenasouza3131 Год назад

Amazing Explanation! Thank you very much.

@Alejandro-db2xj 3 года назад

Empeze viendo un video especifico y termine encontrando un canal con excelente contenido y perfección al explicar! Muchas gracias

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

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

@MrDidaxi Год назад

Smashing. Thank you!

@jessicathomas73 3 года назад

This is truly brilliant - thank you!

@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 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-EKKFLt5ItKc.html

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

@charlesngan8041 Год назад

Always a joy to watch and learn from your videos

@nonoobott8602 2 года назад

The way you explain very clearly is amazing. Learned so much from your Channel. Thanks for sharing

@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

@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 5 лет назад

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

@robertcortes2315 3 года назад

im just new in these application and found these 11/10 tutorial, thank you for this Ms. Leila.

@messaoudpolitique9208 Год назад

Simple easy to learn really the best I see till now

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

@rotethongvanit Год назад

A function I've been looking for. Thanks.

@rbfreitas 4 года назад

Always a great tutorial!!!thanks

@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 года назад

ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-fDB1Ktyhp3Y.html this can act close to a filter function!

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