Many SEARCHABLE Drop-Down Lists in Excel (No VBA)
Вставка
- Опубліковано 24 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Enhance your Excel sheets with searchable drop-down lists, ideal for situations where you need dynamic and interactive data entry. This tutorial is especially useful for creating multiple drop-down lists on the same sheet, tailored to professionals who manage large data sets or require efficient data entry methods.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/multiple-se...
🌟 Key Learning Points:
- Searchable Drop-Down Basics: Learn the fundamentals of creating a searchable drop-down list using Excel's dynamic array formulas, a method suitable for single-cell applications.
- Replicating Lists for Multiple Rows: Discover techniques to extend your searchable drop-down lists across multiple rows, ensuring functionality throughout a larger section of your spreadsheet.
- Data Preparation Table: Understand the importance of creating a data preparation table, which involves adjusting your original data set to suit the needs of the drop-down list.
- Using Dynamic Array Formulas: Explore how dynamic array formulas like FILTER, SORT, and TRANSPOSE can be used to craft the necessary list of options for your drop-down.
- Applying Data Validation: Gain insights into applying data validation to multiple cells, allowing each cell to have its own individual searchable drop-down list.
- Bonus Formula for Related Data: Learn an additional formula using XLOOKUP to display related data (like company names) once a selection is made from the drop-down list.
🚀 Practical Applications:
- Create efficient, user-friendly data entry points in your Excel sheets.
- Implement searchable drop-down lists for tasks like inventory management, survey responses, or any scenario requiring a selection from a large data set.
- Enhance your Excel reports or databases with dynamic, interactive elements.
00:00 How to create multiple searchable drop-down lists in Excel
02:00 Combine with legacy Excel formulas like the SEARCH function
08:30 Transpose functions
10:40 Bonus XLOOKUP function
★ Link to complete Excel Dynamic Arrays Class: www.xelplus.com/course/excel-...
Check out the complete Excel for Office 365 playlist: • Excel for Office 365 &...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 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
Grab the file I used in the video from here 👉 pages.xelplus.com/multiple-searchable-dropdown
I really like the way that you explain each step and working the formula from the inside out. It helps me to understand what is really going on.
Leila, Thank you for the videos. Having watched your videos for the last three years has improved my excel skills. I appreciate your work. You are indeed a true practicing economist, who define public goods as non-rivalry and excludable. You sharing your knowledge with us doesn't reduce your content level.
Wonderful! Thank you for the kind feedback!
EXCELent performance…. incredible results and miracle of Excel...Dynamic array functions are useful getting these results.
Thanks Leila and Microsoft Excel team 👍✌
What can I say that has not been said already you opened my eyes to so many options, explaining with so much clarity!! and solutions thanks a million
I just recently found out about this channel, its excellent, and you are an excellent teacher, it would have helped me a lot if I found it sooner. Your videos are extremely clear, and your delivery method, and production is top notch. Infact I can comfortably follow your videos at 3.3 times with perfectly legible audio, while I cap at 2.5 for most other tutorials.
Thank You.
Once Again... You answered my pending question when I saw the first Searchable Drop Down List you posted. You Are The Best!!!
I have been searching for a resolution to this specific drop down list scenario for a long time. Your videos are great and you do an excellent job explaining and walking through the steps in setting things up. You have always been and remain my "go to" when I have excel questions.Thank you so much.
I actually did this today before seeing this video and came up with the same solution except I made it via Columns as opposed to transposed. That’s a great solution. Something I noticed was that the size of the spreadsheet was huge and a way around this was to set the spilled range to NULL (using an IF statement) unless the corresponding validation list had an entry in it. The difference in size was staggering.
I love your videos. Thank you
Could you paste here your solution?
You're really brilliant Ms. Garany for solving this out for all of us. I appreciated
Leila, you are a magician. I’m in love with Excel for more than 20years but you never failed to impressed me. I’m learning something new every time i watch your great videos. More importantly, while watching i’m smiling from start to end. It is really very entertaining 😂.
I wish i can meet you in person one day.
Many thanks for the kind words Ahmed. It's great that you're still dedicated to learning.
Leila, THANK YOU!! You have solved my problems! Love you videos, You process is so easy to follow and understand.
Hi Leila..... Amazing tutorial... thanx for all the wonderful knowledge that I have gained from you......If the reference report!b5 is replaced with function Cell("contents")....this will get value to be searched from the active cell. Now now the dropdown can be copied other cells.....No need to transpose and other steps....
Liking in advance. Will watch later in the evening. Thank you Leila, always.
What a wonderful logic in this solution! I wish we had an inbuilt Search feature in Excel, so it doesn't require data preparation tables
I set out looking for this possibility this morning not even knowing what to call it. I found your first video on it, which helped, but needed the feature on multiple rows. Then I found this video and feel so lucky on my timing since you just put this out. Thank you! I will be subscribing and can't wait to check out your other videos.
Welcome! Glad the videos helped :)
That is a complicated solution that requires a formula with a number of functions. Thank God you are here to hold us by the hand through the entire process.
Love your pronunciation of the word "character". ;-)
You are very welcome :)
Just yesterday evening I tried to figured out how to create multiple-row searchable drop-downs using dynamic arrays. Finally i did it hard way, by using indirect address, multiple columns and manually setting up all data validation for every cell in the range (Was nightmare:P) And today's morning i just found this tutorial...Transpose...PasteSpecial-Validation...Brilliant!
I feel so stupid now:) Great guide, just like always!
Wish I'd seen this earlier. I used columns for dv prep which made replicating DV time consuming and prone to breaking. I don't know how I missed the Transpose formula - will help clean up the DV prep 😀
Thank you!!!
I've only recently discovered your channel. Your Searchable Drop Down List video showed up in my recommended list and answered my long-time question if there was a solution without needing to know VBA. At the end of watching, I was asking myself, "But what about creating multiple searchable drop down lists?" Lo and Behold, the UA-cam algorithm served up the answer with your follow up video. You are a great instructor, and I look forward to checking out your other videos. You have a new subscriber!
That's great! Welcome aboard :)
Great video as usual. Thank you so much for making these videos. I've been a fan for a couple of years now and have even taken several of your classes.
Great to hear Mark! Thanks for your ongoing support.
This is a brilliant video tutorial for beginners like me. Thanks! But as i searched more, i noted that a much simpler way to achieve this from your last video would be to use the concept of "active cell" (CELL("content") formula) in your search formula and write one line of code in vba (application.calculate). I know this says "no vba" tutorial but since its that small a piece of code, you can mention that in such videos so users can choose wisely.
Most people avoid VBA, not because of the amount of code, but because it requires your workbook to be macro-enabled. These aren't always safe to share as they can contain malicious code, so many companies will block these. So, just because it is only one line of code, that is too much.
Thanks for your video!
I found a way to use multiple searchable drop-down lists with only one data validation prep list, that should reduce the file size if you have many drop-down lists.
If you input the formula =CELL("contents") into a cell, that cell is updated with the most recently updated cell value in the worksheet.
All I did was following the guide, using the formula =IF(CELL("content")=0,"",CELL("content")) in cell D1 and instead of Report!B5 in cell D2 I used D1.
With that IF-function included, the formula returns "" instead of 0 if the last updated cell is empty. An important change to be able to browse the complete list with no text entered.
I tried this option and works almost perfectly, but when I go to the next cell to fill, it filters and only displays the values that meet the past criteria.
You said that with the IF function it would be solved, but that doesn't seem to be the case for me.
Do you have any advice?
Very good explanation. I was looking for something like this. Thank you for many helpful videos. Can you do a video using multiple (about 20/worksheet) searchable drop down lists on 20-30 worksheets and all worksheets using the same master list from sheet 1 in same workbook?
I have added new learning because of you Ms Leila. Your excel content is very helpful for my channel EXCEL-ACCOUNTING training. I am excited to learn new excel tricks so I can apply it in my Accounting Tutorial using excel because I want to grow in youtube like you. You have a new subscriber here :)
As always, thank you Leila! Transposing the filter spill is a great idea! thanks
You're so welcome!
This is exactly what I was looking for. Thank you for this great video. You're my excel guru!
Glad it was helpful Joanne!
Fantastic! I had tried using the cut and paste special to transpose, but that doesn't solve the issue. I didn't even think of transposing the spilled range! Well done and thank you.
Thank you Leila for your immediate response. Actually I am having the monthly subscription pack for Office 365 Personal version. Kindly clarify
Miss Leila, you're just making our jobs so easy. I ca'n't thank you enough,
It's my pleasure! :)
I Love your Tutorials, They made my work to function so Smooth. THANKS A LOT !!!!
Great to hear!
always Love your video...thx Leia...I Learn more about excel from you everyday...and I found another solution to these by using CELL function...well it has a weakness, but it better than copying the formula for each row...you should look into it...
Leila, you are a genius. Love your videos. Thank you so much.
Hi
Been a big fan of your videos for a couple of months now.
This transpose solution is just genius. Have seen other videos but solutions were too complicated.
If you ever think about spreadsheet size, mine went from 160 kB to almost 300 kB with your approach, I solved this with an IFERROR wrapping all the transpose function.
Only drawback I see is that the drop down doesn't feature a single name unless you type a single letter, it is a minor one for me.
Super helpful video. Great Job Leila ! Thank you very much.
I'm grateful for this.Thank you Leila.
You....I send you so much love from Cameroon... appreciate what you're doing
This is just perfect and worked marvellously! Thanks for sharing!!
Thanks a lot!!! Exactly what I was looking for!
By the way, Leila, how would you be able to create multiple dependent dropdown lists for many rows like that?
Got introduced to so many new functions I was unaware of. Thanks!
Happy to help!
Thank you for this insightful and useful video, it was a solution that I have been searching for, but the only issue I am encountering now is the inability to have this replicated as a copy and paste in multiple sheets in the same workbook
!!!
Thank you so much .. Amazing demonstration and very easy to apply .. Saved me from a lot of headache.
You are welcome!
Thanks for all the helpful videos; I have gained a lot of useful insight from your channel that helps me interact better with Excel. Sorry if my question below is out of context: what areas of Excel do you think I need to know/master to be ready for a Data Entry role? I'm trying to start a new career in Data Entry
Leila ... this is an amazing technique ... thank you very much ... i learned alot today just from one video
Glad it was helpful!
You are the best 😘😘😘. Exactly what I have been looking for. You just gave me a good night rest.
Thanks Leila , I learned a lot with your video, Now I'm cracking this thing
The best Excel experience as always
Chapeau Leila, really great work as always
Thank you! 😃
Great solution without VBA. I enjoyed your uTube trainings.
Excel has become really efficient since 2018! Very fast improvement!
That's true. Dynamic Arrays was a big step in the right direction.
lol i am here because i watched your other video and need to do the search in multiple rows! THANK YOU!! :)
You're very welcome!!
nice methodical explanation. Very easy to follow. Thanks!
Brilliant, very good explanation. I was looking for this long time ago, many thanks.🌺🌹
Thank you for this video. Very well explained and very useful.
Worked like a charm, thank you very very much. Keep those helpful videos coming up.
Glad it helped
Hello Leila, thank you a lot for all videos you are doing! I would like to also know if is possible to make searchable dependent dropdown list. Thank you a lot for help :)
Fantastic video and solution!!! My question is, how can I incorporate this for use in a Userform combobox or list?
My other query is, (in a userform), how can it be set up so if an entry doesn't match, that the user can ADD a new entry.
For example: the user is looking for a company but it's not in the list, how can the user ADD a new company to the data range from within the userform?
I think a lot of peoole would find it very useful ... I've searched the web but haven't found a proper solution for use in a userform.
I love your videos, easy to understand, practical and beautifully presented - thank you for the time and effort you put into your lessons and making them available freely. BRILLIANT WORK ☺
Amazing!! Can't express my appreciation for this video!
Glad you liked it!
Thank you for this video! Very useful for something I'm working on for work.
At last I have a solution for my problem. Thank you very much
You inspired me a lot. Because of you I was able to gret the job title that I want. 😍
Wow, that's great. Congratulations!
Thanks Leila. I have been using your first solution successfully for a couple weeks. This transpise solution gives me more options.
In my solution I added a column in my master data table where I used Concat to join 3 separate columns. I then used this new helper column to allow me to search text on first name, last name or company name. My XLOOKIUP could then return company ID. It works well.
Thanks for sharing your solutions. Keep up the great work.
Fantastic! It's great you could implement it successfully.
Haha, I came up with the exact same solution! Was hoping for an alternate method, Now I've got a problem where multiple sheets exist so I will have to create multiple preparation lists for now. If you do come up with another way to do it, please make another video! :D
Thanks a lot! Exactly what I was looking for
Hai Leila, I saw another method to create a simple searchable drop down list. Just create a pivot table with values in the filter section only. This will act as a searchable drop down list with zero duplicates. This cell can also be referred for formulas.
Hi, can you show me a video about this? Interested
This was so helpful! Thank you kindly.
Hi, Leila. Thanks a lot for this video. If I may ask, can you make a video about 3 Dependent Dropdown List that I can apply to multiple rows.
Wow, been looking for this (and trying to do it on my own - got close). I followed your instructions and it works great. Is there a way to when you start typing that the dropdown list appears (maybe after 2 letters?) and as you continue to type the list gets less and less?
Thanks much for sharing this video and let me know about my question. Meanwhile I will try something on my own. Thanks again.
thanks a lot ma'm for the solution , of this problem i was looking for a long time. great
My data entry form is an Excel table, hence to cater for dynamic data range. I'd suggest to use the INDIRECT(CELL("address")) feature within the SEARCH formula. This eliminate the needs to Transpose data and no need guess total rows needed.
Hi, can you please advise how you used this? I cannot use transpose because my results are over the columns limit.... Thanks 🙂
thank you so much, i was looking for this formal
Very informative... but i dont know where to use it first... thanks for sharing ur knowledge as always
thanks Leila for an excellent explanation. pls advise how to implement such a solution for older versions of excel which doesnot have 365 version
Thanks so much Leila you are very helpful and self challenging
Very useful information. You are the best....
I've been waiting for this!
WoW, really great idea, thank you so much, it's so helpful
Great video! exactly what I needed. Now I need to select multiple items on each cell is this still possible? if so, do you have a video showing this?
THANK YOU Leila! you are incredible smart!
Thanks this is great! Any way to apply this to an Excel Table with an undefined # of rows? I am struggle to understand how to scale the solution as the table grows. Seems like we have to drag the formula down when a new row is added.
Thanks Leila for this great solution!
Very instructive !! I have tried a different approach and works , replace in the data val prep formula B5 with INDIRECT(CELL("address")), no need of transpose or anything.
How does the full formula look then?
can you elaborate please...
What do you mean?
@@D6uzman6 Sorry!!My bad! Did not explained detailed enough previous post. More people were asking how it works.
On "MasterData" worksheet formula in cell D2 should be:
=SORT(FILTER(TableCustomer[Customer],ISNUMBER(SEARCH(INDIRECT(CELL("address")),TableCustomer[Customer])),"not found"))
It´s the same Leila's formula without the TRANSPOSE , and instead of reference "Report!B5" we put this INDIRECT(CELL("address")). What does this? the CELL("address") returns the address of the current cell wherever the cursor is in our workbook at ANY time, and INDIRECT returns whatever we type in that cell. Magic!
Now on "Report" worksheet all the cells from B5 to B19 should have as Data Validation the same formula
=MasterData!$D$2#. You can modify it first only in cell B5 , then select B5, right click, copy , select range B6:B19, right click , Paste Special, choose Validation, OK.
Hope that this explains everything.
@@anjaogper8907 sorry for replying late. somehow missed the notification. Please check beneath the answer.
i need this and u have solved with transpose ...great mam.
Thanks a lot!!! Exactly what I was looking for!
Thank you Leila. The way you explained is awesome. I resolved my challenges with your formula. However, it increase the file size drastically, any solution for that?
Nice approach. Thank you so much.
That's a great solution Leila, just need to wait for those functions to come in my version of Office 365
If you're on the semi-annual update you should get it by July.
@@LeilaGharani Love you Leila 😘
Stay home and stay healthy 👍
ua-cam.com/video/e2-uc3nOKlE/v-deo.html for old version do excel
Incredible Leila, thanks for sharing this!
My pleasure!
Brilliant. Thank you so much!
Hi Leila.. cool solution. Thanks for sharing it. I was intrigued to see if I could duplicate it without dynamic array functions. Came up with this:
On your MasterData sheet in cell D2: =IFERROR(INDEX(TableCustomer[Customer],AGGREGATE(15,6,(1/ISNUMBER(SEARCH(Report!$B5,TableCustomer[Customer])))*(ROW(TableCustomer[Customer])-ROW($A$2)+1),COLUMNS($D2:D2)))," "). Copy this down to row 32 and right to column AH. With the cell pointer in cell B5 on the Report worksheet, create a named range (I called mine myDropData) with the following formula: =MasterData!$D2:INDEX(MasterData!$D2:$AH2,COUNTIF(MasterData!$D2:$AH2,""&" ")). Then define Data Validation for B5:B19 on the Report worksheet as: List and =myDropData and turn off the error alert for invalid data. Also, for C5 enter: IFERROR(INDEX(TableCustomer[Company],MATCH(Report!B5,TableCustomer[Customer],0)),"") and copy down to C19 to match the selected name with the company or a blank if no match.
The above pretty much works like yours, except that the drop list data is not sorted.. although you can sort it at the data table level to achieve the same. If there is no match to what you type in the data validation cells in B5:B19, you get a drop down with nothing in it (all the " " blank spaces from IFERROR). Otherwise, it has the same functionality.. although more difficult to set up without dynamic array functions. Anyway, I love your technique and it was a great inspiration to create something for those without DAFs. Thanks for all the good learning at your channel and thumbs up!!
The best, thank you
Thanks for your tutorial they are very helpful, other option would also be to apply Search ( INDIRECT(CELL ("address")), etc...
then in name range then copy in the cell validation wherever you want and voila ( c'est plus rapide) ou ajouter un peu de VBA (no need to transpose).
can you elaborate please...
Thank you for this, helps me a lot. How do I add this to a second column in the same worksheet using the same master data info?
Thankyou very much for sharing and teaching.
You are very welcome
Thanks Leila. Do you think there is a way to automate the "Data Validation Prep" list using the UNIQUE formula? Say you are getting a disctinc list from an existing table and you want to use that as your data validation prep.
You are amazing, this is exactly was i looking for, the transpose do the trick :D
Great!
Thank you for the great info!! I have implemented a variation of this for 300 rows of lookup on Sheet2 where Sheet1 uses the data validation. My actual lookup table is 1500 rows. I am getting a number of reports back from my users that using Column Filtering on Sheets3-5 have slowed down to a minute or so after adding the transpose=>Sorter=>Filter to support 300 rows. Do you have any suggestions to help bring performance back to the other worksheets in the workbook that are not using this capability?
Great Leila. Thank You!👍
Huge thanks man, seriously.
You are always making amazing videos ✌️👌😁. But, I am currently struggling with Google sheet online in which I am trying to create a protection in the enter sheet only those cells and columns that has a specific word, so in a nutshell, I want to make automatic protection based on conditional. For instance, I have a data entry in which the team leaders fill it and I have a column to approve them, if I said yes then automatically lock these rows for them, and if it was no then leave it as it is. Thanks in advance. I have searched a lot but didn't find anything like this.
Thank you so much . great and that’s what, i was looking for.
Glad it helped!
Thank you! Really helpful ❤