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: th-cam.com/video/3Qh5nUmCVd8/w-d-xo.html For Searchable Drop-Downs on MULTIPLE rows check out this video: th-cam.com/video/waqzwMCYD9I/w-d-xo.html
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.
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")
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.
@@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 :(
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.
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!
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.
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.
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 !
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.
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.
@@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
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
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!
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.
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.
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?
@@rachmadhusnianto3506 - Found another tutorial that helped me to create a searchable and dynamic dropdown function. th-cam.com/video/fsL57bvd7Pk/w-d-xo.html
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..
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.
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!!! 👍
Leila this is amazing, thank you so much! I had several rows with a dropdown list and to filter in every one of them I edited the formula and added CELL so the reference of the search is whichever cell I'm on top of at the moment. =SORT(FILTER(J3#,ISNUMBER(SEARCH(INDIRECT(CELL("address")),J3#)),"not found"))
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 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.
@@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.
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.:)
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.
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?
@@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
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!
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!!!
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.?
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.
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?
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!
Your is the best excel classes on TH-cam 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
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
@@hexsplays 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.
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.
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...
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!
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?
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!
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 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?"
@@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!
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.
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...
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!
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 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.
Leila ... I am excited I found you Today !! You taught me so much ...I will purchase your Course above soon. Awsome Thnx for making my life so much easier
Great. Thank you. Made me curious. By replacing "Report!B5" @ 7:09 with "Indirect(cell("address"))" it is possible to use the searchable drop down list from various input cells. Just go to the cell you want to use for your Input and hit F9 to activate it (same validation list should have been applied before to the cell).
Thanks for this. One more challenge is involved for copying this validation to multiple cells vertically in a table. Data validation with reference to a particular cell without fixing $ sign works (Say =f2#). however the dynamic arrays give spill error as temperory arrays start overlapping each other. I overcame this error by using transpose function on the argument (so that array spills horizontally. Further I also used output limit to top 5/top 10 to reduce large number of starting points in case only few characters are entered. you may like to showcase this on your video.
With Google Sheets, you don't even realise that it is all done for you already plus many more good benefits (in the data validation list in particular, like not showing empty cells, and also the list is being filtered while typing - no need to click the drop-down again and again). What a shame that Microsoft is still trying to catch up on these simple modifications.
Dear Respectful Leila, Am using the Excel 2016, I had worked your step, but the issue is that I couldn't find the function "FILTER" I can only find Filter xml which is wired, please advice, Many Thanks
I got this working partially because the sheet with the data preparation filters the data as expected, but on the main sheet where the dropdown is applied, all I get is a full dropdown list with none of that fancy filtering. OK, I see now. I had the D1 reference still in there, but I have to do it that way because I have about 20 different dropdowns with that same data in the drop down. In order to do what I want to do, I would have to create 20 different data preparation areas on that Master Data sheet! That's ridiculous, there has to be a better way. UPDATE: It was easier than I thought to create the amount of data preparation area's. I just had to lock the cell ref for Column A part of the name data range, then just drag the formula accross to the right as many times as I needed. Brilliant!
OMG.... This is the Holy Grail. I have a massive VBA solution to this (which I never use BTW), but THIS IS AWESOME Sharing this one with the team right now!
You are great, you helped me so much, and i simply have to click on subscribe. You have earned it within one minute, that is how good you are. Once again million thanks, and greetings from Serbia
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.
Hi Leila, your video is phenomenal! I have finally found the solution that works. Thank you very much for your teaching. My name's Benedetto and I'm in Italy. I LOVE YOU. THANKS THANKS THANKS
(apologize for my English) Dear Leila, I would know if it would be possible to set the reference to the Cell containing the value to find (in your example Rob in B5) in a dinamic way. If yes, how? In other words, if I add a new row to the referred Cell (it changes from B5 to B6, for example), is it possible to change the reference automatically in the formula? Thanks for the time You will spend for answering.
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: th-cam.com/video/3Qh5nUmCVd8/w-d-xo.html
For Searchable Drop-Downs on MULTIPLE rows check out this video: th-cam.com/video/waqzwMCYD9I/w-d-xo.html
Thanks for editing this comment. After trying this out last night, I was confused as they are already searchable.
Content - 10/10
Way of Explaining - 10/10
Your knowledge and teaching skills are extraordinary!!
Fully agreed!!
filter arry is not showing in my formula ,please help
And your buttering- 11/10
Annnd i just subbed
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.
Great video. The question I have is that if I want to create a dynamic drop down for multiple cells?
I have also same query that this method does not work if e want to drag and drop same data validation to multiple cells
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")
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.
@@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 :(
Thank you so much Leila. I have learned a lot from your videos.
Big hug from Portugal.
You're so welcome!
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.
Thank you very much!
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!
.. and this is the comment that helped me to figure it out on 2013. Thank you wouter114!
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.
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.
Wow, thanks Luis!
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 !
Thank you very much for the kind feedback Alexandre!
I bless God for the day i found you Leila. Amazing tutorials.
Thank you for the kind words!
Osamudianmen Igbinovia hope she see this brogod 💯💯
Ditto
This channel is superb. Would recommend it to all excel users.
I had watched office tutorials since years, and without any doubt you are the best
THAt's so great! Thanks a lot
I would like to express my gratitude for the outstanding tutorial you shared.
You're very welcome!
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.
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.
remove the f4 function for cell in the validation
Hello Angelo can you please explain bit more . How can we achieve this for whole column
@@angeloaquino1121 hello can you please explain bit more that how can we achieve this for whole column?
@@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
Hi Leila, would you mind to show us how to deal with multiple cells in a column using this searchable drop-down list?
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
@@SaddamKhan-jd2sj It is not working in that way.
@@SYEDMOHAMMMADKASHIF do you know the answer?? I'll have to practice this tomorrow and try to find an answer.
Please help Leila! :(
@@SYEDMOHAMMMADKASHIF there is already a video by Leila, just search: Many Searchable drop-down lists
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!
Many thanks for the kind feedback. It's great to have you here :)
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.
That's a perfect use Shawn. It's great that you were able to implement this for your purpose.
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.
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?
same question here..
@@wesverg658 is there an answer for this quest ? i have the same and need feedback.
is there an answer for this quest ? i have the same and need feedback.
many thanks
Have you get the solution of this?
@@rachmadhusnianto3506 - Found another tutorial that helped me to create a searchable and dynamic dropdown function.
th-cam.com/video/fsL57bvd7Pk/w-d-xo.html
How does this work in a table with drop downs?
You are brilliant, even after years of using Excel I always learn a few things from your videos.
after watching many of your and others videos, your the best, clear, simple and thorough, becoming a fan....
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..
Totally mind-blowing that this material is free of charge. Great content, keep it up! :)
BRILLIANT! Great solution to a long-time need! I've had to do this in the past and it was a crazy mess.
Thank you! Yes - agree - it was a crazy mess before :)
Thank you
Hi Leila, Great function, However I get an error message when adding the "FILTER" part.
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.
Did you solve this??
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!!! 👍
Leila this is amazing, thank you so much!
I had several rows with a dropdown list and to filter in every one of them I edited the formula and added CELL so the reference of the search is whichever cell I'm on top of at the moment.
=SORT(FILTER(J3#,ISNUMBER(SEARCH(INDIRECT(CELL("address")),J3#)),"not found"))
I have the same problem, but new to this so do not really follow your solution. Can you elaborate a bit on this please.
This is just what I needed. I would still be searching for this if not for your comment. Thank you! And thank you for the great video!
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)?
Hi McKay, did you solve your problem? I am having same issue and looking to solve it as well..
@@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.
@@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.
@@McKaySavage Have you considered using Slicers?
@@sarahogan1715 how would slicers help with an input form with multiple rows per form?
Great video, learned a lot. I have a question, how this data validation can be replicate in other rows? thanks
th-cam.com/video/waqzwMCYD9I/w-d-xo.html
@@debjitpaul3399 Thank you!
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.:)
Thank you very much for the kind feedback!
Definitely, you are the best Excel teacher ever !
Most of your Excel videos are worth the time, your teaching skills are 10 / 10.
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.
Thank you very much Gopala for your kind words and your support. I'm very grateful for the Excel universe.
@@LeilaGharani Great Salutations once again to my Excel Guru. Thank you
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?
did you solve this problem? i have the same question?
@@rcsamaral yes, she has the video on her channel that focuses on this question.
@@ianbarnette2071 which Video is that? I have the same issue
th-cam.com/video/7mo4COng7Sg/w-d-xo.html
@@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
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!
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!!!
Wow, thank you!
You are the greatest excel teacher online, my work has improved so much by watching your videos
Wow, thank you!
Filter function doesn’t seems to be available for general public. How can we add this on our excel?
I think you need to have Office 365 installed.. I have Office 2016 and do not have the FILTER and SORT functions available.
You can use a simple IF function.
@@bluceree7312 how?
@@PaulDewseth Its been a long time can't remember but I think its actually not possible using IF.
@@bluceree7312 its fine, i found a solution for my current problem. thanks buddy
I don't have Filter & Sort function in my excel, can u advise me how to install it? I use office 2016
$ office 365 $
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.?
+1
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.
You are one of the best teacher I have ever studied from... Your way of explaining is very understandable....
Thank you for the kind feedback!
Always a joy to watch and learn from your videos
Best video for searchable drop down list!Awesome!!!
The function FILTER() isn't working in my version of Excel 2016. Any suggestions please?
The new formulas will not come to Excel 2016, only Office 365. See 1:15
Is there any another option?
@Life Goes k, thanks for explanation.
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.
th-cam.com/video/fDB1Ktyhp3Y/w-d-xo.html this can act close to a filter function!
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?
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!
Your is the best excel classes on TH-cam 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
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
Copy that cell and paste special. Validation.
@@hexsplays done
@@hexsplays 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.
@@lordstevent9030 she has an updated video...Many SEARCHABLE Drop-Down Lists in Excel (No VBA)
Thank You Leila, another question what formula's could one use to get the same results that you're demonstrating
Mywy
Hng y
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.
Fantastic leila...Love new approach of Filter function!!!
Simple easy to learn really the best I see till now
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...
It's working for only one cell I need similar data validation for A1:A10 cells please advise.
I need a solution for this also.
@@lordstevent9030 Yes, can it work for more than one cell? I was hoping it would until i tried it
Is there a posibility for auto-fill-in at the moment when you begin typing?
My favorite show :) Not with Excel formulas - Excel calculations run after we click - this gives the data prep list the opportunity to update...
@@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
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!
Microsoft should replace their entire excel help content with your videos :) The best online help available, well paced and gracefully presented
Wow, thanks!
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?
right around min 25 here th-cam.com/video/srTteYoqcJs/w-d-xo.html
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!
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...
Hello Chuz Lahoria, take a look at my answer.
I hope this help you.
@@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?"
@@kirtanpandya Here is the link to my topic: th-cam.com/channels/Ao3Xf4NBuQxuSI1K7kADiw.html
@@jorgemacgenity1367 your link takes us to a blank, page, but I'm interested in this answer!
@@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!
Thank you very much. The way you teach combine many contents(formulas) to solve one problem is very good.
Never have I watched an Excel training this long. Watched at 2x speed. Absolutely full of gems. Really well done -/ thank you!!
Great to hear!
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.
th-cam.com/video/EKKFLt5ItKc/w-d-xo.html
I don't have Filter formula in my excel, what is the alternate formula ?
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...
th-cam.com/video/c0CAlxo_BRk/w-d-xo.html
"=FILTER" is not available on my 365
did you find any solution for this problem, as I also face this filter function not present in excel 2016
I have the same problem did any one find a way ?
Thanks
This function has not been made available for all regular users of office but limited to Office 365 Insiders as stated in the Microsoft answers site
yeah, the same problem. Anyone found the answer please?
@@rsnphenix do you even read?
Thank you so much for sharing so many of these Excel tips and tricks
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!
Many thanks for your support!
You are incredible - I am learning so much from you. Easy to follow, enjoyable - thanks so much!
I did not find filter formula suggest another formula for the same work
I think it's new formula we must update. Office
I was wondering if I could use "Goal Seek" as dynamic function to repeat it in each cell.... Thank you
This is fantastic. I have just used this for searching and filtering and assigning 10,000 sku's of inventory. Awesome! Thanks Leila...
Glad it's helpful for your work Matt.
So Filter function will kill VLookUP, very nice!
Mostly yes - unless you just want the first match or you just have one match and you don‘t want your results to spill.
Thank you very much for your repply Leila! I admire your work.
Hi' I don't have the Filter Function in my Excel, instead its showing Filterxml.
@@BelleRiverHeating how can I be an insider or can any one be an insider?? please help
@@BelleRiverHeating ???
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?
I've got the same problem, please help us....
@@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.
@@ImranKhan-vb7ke hi Imran, I have same issue with excel 2016, it available FilterXml; filter function is only for 365?, thanks.
Did you fix this?
Your tips are different and so striking… amazing explanation.. like always.. Leila..!!
Glad you think so!
Leila ... I am excited I found you Today !! You taught me so much ...I will purchase your Course above soon. Awsome Thnx for making my life so much easier
You are so welcome!
That's super cool ! i can make the telephone list fancy in my company :)
Thanks keep staying awesome Leila!
ma'am can u show how to prepare school time table automatically .....
Great. Thank you. Made me curious. By replacing "Report!B5" @ 7:09 with "Indirect(cell("address"))" it is possible to use the searchable drop down list from various input cells. Just go to the cell you want to use for your Input and hit F9 to activate it (same validation list should have been applied before to the cell).
Thank you @Da Fu. works fine for me. Cool input.
In the German Version you have to replace *Report!B5" with "INDIREKT(ZELLE("adresse"))"
Thanks...genious solution
Thanks for this. One more challenge is involved for copying this validation to multiple cells vertically in a table. Data validation with reference to a particular cell without fixing $ sign works (Say =f2#). however the dynamic arrays give spill error as temperory arrays start overlapping each other. I overcame this error by using transpose function on the argument (so that array spills horizontally. Further I also used output limit to top 5/top 10 to reduce large number of starting points in case only few characters are entered. you may like to showcase this on your video.
filter doesn't working (is there any other function)
With Google Sheets, you don't even realise that it is all done for you already plus many more good benefits (in the data validation list in particular, like not showing empty cells, and also the list is being filtered while typing - no need to click the drop-down again and again). What a shame that Microsoft is still trying to catch up on these simple modifications.
Im using MS 365 ProPlus but Filter function is not avail in MS. 😏😔
For now it is only available in the Insider Edition of Office 365.
im just new in these application and found these 11/10 tutorial, thank you for this Ms. Leila.
You are really good helping people to learn Excel for free... thanks so much
You're very welcome!
Dear Respectful Leila,
Am using the Excel 2016, I had worked your step, but the issue is that I couldn't find the function "FILTER" I can only find Filter xml which is wired, please advice, Many Thanks
WHen pressing ENTER a message pops informing me "That Function not Valid:
Filter is only available in Microsoft (Office) 365. It will not come to Excel 2016.
@@LeilaGharani Thanks, it was highlighted in your video, but I was double checking, regards
HI. How can we get multiple searcheable dependent dropdown lists with dynamic arrays ?
Try this to bring search in google sheets to the next level with keywords name and birth date th-cam.com/video/XOzRCwyCkdA/w-d-xo.html
Amazing, many thanks!
THIS SOLVED MY PROBLEM AND WILL BE PART OF MY ANNUAL PERFORMANCE REVIEW!! THANK YOU!!
Glad it was helpful!
Amaizing soluction Leila!!!
I got this working partially because the sheet with the data preparation filters the data as expected, but on the main sheet where the dropdown is applied, all I get is a full dropdown list with none of that fancy filtering. OK, I see now. I had the D1 reference still in there, but I have to do it that way because I have about 20 different dropdowns with that same data in the drop down. In order to do what I want to do, I would have to create 20 different data preparation areas on that Master Data sheet! That's ridiculous, there has to be a better way. UPDATE: It was easier than I thought to create the amount of data preparation area's. I just had to lock the cell ref for Column A part of the name data range, then just drag the formula accross to the right as many times as I needed. Brilliant!
OMG.... This is the Holy Grail. I have a massive VBA solution to this (which I never use BTW), but
THIS
IS
AWESOME
Sharing this one with the team right now!
Glad it's helpful. I hope you team will like it :)
@@LeilaGharaniThey will like it ... once we all get the new version of Excel!
You are great, you helped me so much, and i simply have to click on subscribe. You have earned it within one minute, that is how good you are. Once again million thanks, and greetings from Serbia
Thanks for the sub, Milan!
درود بر شما سرکار خانم قرنی .بسیار خوشحال هستم که کانال آموزشی شما رو پیدا کرده ام. برای شما ارزوی تندرستی و بعد موفقیت بیش از پیش دارم.
Thank you for sharing so many ways to work Excel.
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.
Glad to have you aboard :)
Hi Leila, your video is phenomenal! I have finally found the solution that works. Thank you very much for your teaching. My name's Benedetto and I'm in Italy. I LOVE YOU. THANKS THANKS THANKS
(apologize for my English) Dear Leila, I would know if it would be possible to set the reference to the Cell containing the value to find (in your example Rob in B5) in a dinamic way. If yes, how? In other words, if I add a new row to the referred Cell (it changes from B5 to B6, for example), is it possible to change the reference automatically in the formula?
Thanks for the time You will spend for answering.