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?
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.
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
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!
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.
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.
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 TH-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!
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.
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". ;-)
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.
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.
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.
Always great content, thanks, but I am missing basic Excel features as how do you expand the search results to the entire D1 column when closing bracket/pressing enter at around 2:43 minutes of the video? Mine is not working like that. Thanks
Honestly excel is a nightmare when it comes to taking out complicated results, a simple thing like a searchable drop down list is a nightmare to create and one has to use endless techniques. I’ve been using these basic features and much much much more complicated features in a program called FILEMAKER since over 10 years, that program scripts all these steps for you on its own with very very little steps. Apple really does wonders. And FileMaker talks to excel as well. Sadly I’m using excel as well due to some clients etc not budging from their old ways. Your teaching skills are 10/10 and I’m an absolute fan and subscriber of your multiple courses. Thanks for teaching us in such an organised style.
Filemaker isn't really comparable to Excel though. You'd probably compare it to something like Access. Excel isn't meant to have the functions that Filemaker has, although I do agree that drop-downs could/should be more programmable
Thanks for your feedback Paramveer. It's good to see you on TH-cam too! I wish searchable lists were also easier in Excel. It would be great to get an option in data validation on whether we'd like it to be searchable and also if we'd like to have multiple results in the same cell split with a delimiter of our choice.
@@ricos1497 Sir, I agree with you, but FILEMAKER is cross platform and multiuser. I junked SAP, as the implementation costs were recurring and high. Since I was already into the MAC Environment. I trusted Apple, I bought Filemaker, bought online tutorials, i implemented my own version of an ERP, since I know my business well, I was able to formulate and organise my requirements in the best way, No implementation costs, DEBUGGING is a pleasure and super easy, Much more effective, And best part is FILEMAKER is very forgiving. Even when i made mistakes in the structure of the database, I can make changes easily. Anyway, I saved a lot of money, by switching to Filemaker, and with that money bought myself a Mercedes, And I lived happily ever after.(Based on a true story)
@@LeilaGharani Honestly, Ive bought a lot of EXCEL tutorials, YOURS are by far the best, Very Systematic, Easy to Understand, Extremely well structured and Explained. Im glad & lucky to be your student. Keep up the great work.
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....
This is a good technique if the filtered list remains the same size, but as soon as the number of drop downs required increases, you need to add more entries into the filtered list. I do not see a simple way to do this automatically. Any ideas?
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.
@@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.
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.
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...
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 :)
I used a trick I saw in another video to link the helper table to the current cell being used. By using CELL(“address”) inside the indirect function to replace the “find text” cell reference in the formula. It’s still has a bug though; you have to click into the cell to reset the helper table. Hope this helps. =FILTER(Table[data],ISNUMBER(SEARCH(INDIRECT(CELL("address")),Table[data])),"Not Found")
Hello Leila. Thank for this awesome video. Unlike how you are able to automatically update the dropdown options in realtime (when you type and click the data validation arrow), I have to type few letters, then press enter (which goes to next row) and then come back to the cell where partial letters are typed to see the filtered list. What settings do i need to update realtime like yours
This works nicely. Like Bethu, the list does not generate once i type in a few letters; i have to leave the cell and return before the cell generates list. Other than that it works exactly like you describe.
I should restate prior post. When i enter a few letters, the dynamic prep range is created however, when i click the dropdown I get the full list. If i leave the cell and return the dropdown matches the filtered dynamic list. How can I get the dropdown to sync with the dynamic filtered list w/o leaving the cell?
Question/suggestion... 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 entries (only one matching typed letters)?
You might want to try this if you have fewer data. Let's say less than a hundred first please watch this trick: th-cam.com/video/PVEzHbdHf1Y/w-d-xo.html second, create your list and then hide them you will see the sugestion / autocomplete based on what you are typing
I use data validation in each row of my excel database , I have a sheet for my chart of accounts and one sheet like journal entry , when i record some data and by name of one accounts if I don't remember that name its doesn't allow me to continue , if is available tell me how i can search and find the list of that accent that i enter , means when i enter the first alphabeted of some name its show us like a list and I select the name of I want
If you came to this video after watching the original video (Searchable Drop Down List in Excel (Very Easy with FILTER Function), you can skip to 5:36 in this one, as up to that point, it's just an intro and recap of the previous video.
Dear Leila, first of all I would like to thank you for the great work. Your videos are fantastic. The current solution works very well but I'm wondering what I can do to keep the file size reasonable when I would like to use this solution for 1k to 5k rows? Many thanks Mounir
EXCELent performance…. incredible results and miracle of Excel...Dynamic array functions are useful getting these results. Thanks Leila and Microsoft Excel team 👍✌
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
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.
Amazing solution. Your are a genius. Can I ask, is there a way of getting those transposed prepared lists as shown at 8:39 to spill down automatically depending on your table size. I see you selected how many rows you had (something like 15 entries in your case). My table that I need the searchable dropdown list for needs to run for a whole financial year, so there will be an unknown number of rows numbering in the hundreds. Ideally I would like the preparation lists to create themselves as new rows are added to the table, rather than someone having to manually copy down the preparation list as new data rows are added.
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?
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. 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.
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.
Hi, I really like how you explain each step. I do have a question about the 2nd column, what change would I need to make to the formula in a situation where the first column has 2 or 3 items in the 2nd column (cascading dropdown) . Currently I am getting the first item it reads. Hoping you can help with me with this.
Hi Miss Leila, thank you for this technique. I already use and it works. It's just that it's not working if I protect sheet. I uncheck the cells but the arrow is still not clickable. Do you have solution for this? Thank you so much
simple question, I have this all set up and it works just fine except when I type a few letters for my list selection it doesn't auto update the list unless I hit enter and I don't see you having to do that in your videos. So if I type "to" and then click on the drop down list icon I will only get my filtered list if I hit enter or select a different cell and then click the icon.
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
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!!
Dear Leila, would there be a way to make a searchable dependable drop-down list. I've got 20 column's and 2000 rows of nutrition data. I've been experimenting but can't seem to figure it out, maybe other people also have this problem
first of all I thank you for your gracious work it is really helpful and I just want to ask you about some of these functions which are not available in my copy of office 19 so I was wondering how to get this in office 19 since I don't have Office 365
Thanks a lot Leila! That is a very informative video as usual. I have learnt a lot from your Udemy courses. Is there any method to make dynamic searchable lists from source in multiple sheets using INDIRECT in Data Validation? I tried using INDIRECT with # inside brackets, but it doesn't work.
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!!!
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?
Hi Leila, thank you for the great video. A few questions, I tried to use it, but when I type in the target cell for the main formula to work, it didn't update realtime like in this video. which means I have to enter first (automatically exit the cell), before the content from the dropdown list is updated (so I have to reselect the dropdown list after pressing enter). Any idea why and how to fix this? second question, can I use this with a large number of rows? will it be efficient? Thank you
very helpful,. hello leila, I have a question, how do you do that 2:43 just once enter the cell below. Filled with the same formula and does not change. I'm almost desperate to find a way on Google, please reply to my comments as soon as possible. sorry for my bad english
That’s automatic because it’s a spilled range. Sheets has the same features if you use functions that spill like filter or sort functions. But because Sheets doesn’t have table referencing you can’t get the same effect as shown here. You have to pull the formula down.
Thank you so much for replying to my comment Leila ,. but I am still not satisfied, because I want to know how, my Excel can also be like that. I Am Waiting For The Next Video ,. sorry "I'm a Beginner"
Is it possible to create multiple dropdown list in official excel table. To create dropdown list automatically when new row add in that table just lite formula is automatically create. Thank you for your sharing.
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?
it works and it's great But there is a problem, I have much longer list than yours and my excel file become very very slow, suppose it's because of lots of calculation steps. Did you find another way to multiply the SEARCHABLE Drop-Down list or this is the latest idea you had ? thank you
1. Sheet 1 is master sheet of Raw materials and its quantities 2. Sheet 2 is a formulation sheet, Having searchable Dropdown list of raw materials present in sheet 1. 3. My requirement is if I add a quantity of a product in sheet 2, then that quantity should be deducted from sheet 1 quantities. Effectively reflecting the remaining quantity of raw material remaining.
Hi Thank you very much for sharing this video, Have a concern here . If we use this searchable drop down list formula the excel size increases a lot. is there a solution for that
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.
Grab the file I used in the video from here 👉 pages.xelplus.com/multiple-searchable-dropdown
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?
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!
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.
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?
Miss Leila, you're just making our jobs so easy. I ca'n't thank you enough,
It's my pleasure! :)
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!
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 🙂
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.
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
Once Again... You answered my pending question when I saw the first Searchable Drop Down List you posted. You Are The Best!!!
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 TH-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 :)
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
Thanks!
Thank you so much Thomas!
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 :)
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.
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 :)
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.
Always great content, thanks, but I am missing basic Excel features as how do you expand the search results to the entire D1 column when closing bracket/pressing enter at around 2:43 minutes of the video? Mine is not working like that. Thanks
That’s a spill formula, it goes automatically
Honestly excel is a nightmare when it comes to taking out complicated results, a simple thing like a searchable drop down list is a nightmare to create and one has to use endless techniques. I’ve been using these basic features and much much much more complicated features in a program called FILEMAKER since over 10 years, that program scripts all these steps for you on its own with very very little steps. Apple really does wonders. And FileMaker talks to excel as well. Sadly I’m using excel as well due to some clients etc not budging from their old ways. Your teaching skills are 10/10 and I’m an absolute fan and subscriber of your multiple courses. Thanks for teaching us in such an organised style.
Filemaker isn't really comparable to Excel though. You'd probably compare it to something like Access. Excel isn't meant to have the functions that Filemaker has, although I do agree that drop-downs could/should be more programmable
Thanks for your feedback Paramveer. It's good to see you on TH-cam too! I wish searchable lists were also easier in Excel. It would be great to get an option in data validation on whether we'd like it to be searchable and also if we'd like to have multiple results in the same cell split with a delimiter of our choice.
@@LeilaGharani Regards
@@ricos1497 Sir, I agree with you, but FILEMAKER is cross platform and multiuser.
I junked SAP, as the implementation costs were recurring and high. Since I was already into the MAC Environment. I trusted Apple,
I bought Filemaker, bought online tutorials, i implemented my own version of an ERP, since I know my business well, I was able to formulate and organise my requirements in the best way, No implementation costs, DEBUGGING is a pleasure and super easy, Much more effective, And best part is FILEMAKER is very forgiving. Even when i made mistakes in the structure of the database, I can make changes easily.
Anyway, I saved a lot of money, by switching to Filemaker, and with that money bought myself a Mercedes, And I lived happily ever after.(Based on a true story)
@@LeilaGharani Honestly, Ive bought a lot of EXCEL tutorials, YOURS are by far the best,
Very Systematic, Easy to Understand, Extremely well structured and Explained. Im glad & lucky to be your student. Keep up the great work.
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....
Thanks
Many thanks andren!
This is a good technique if the filtered list remains the same size, but as soon as the number of drop downs required increases, you need to add more entries into the filtered list. I do not see a simple way to do this automatically. Any ideas?
Leila, you are a genius. Love your videos. Thank you so much.
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.
Great solution without VBA. I enjoyed your uTube trainings.
Got introduced to so many new functions I was unaware of. Thanks!
Happy to help!
As always, thank you Leila! Transposing the filter spill is a great idea! thanks
You're so welcome!
You're really brilliant Ms. Garany for solving this out for all of us. I appreciated
The best Excel experience as always
Chapeau Leila, really great work as always
Thank you! 😃
I'm grateful for this.Thank you Leila.
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.
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...
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 :)
I used a trick I saw in another video to link the helper table to the current cell being used. By using CELL(“address”) inside the indirect function to replace the “find text” cell reference in the formula. It’s still has a bug though; you have to click into the cell to reset the helper table. Hope this helps.
=FILTER(Table[data],ISNUMBER(SEARCH(INDIRECT(CELL("address")),Table[data])),"Not Found")
Hi, can you please advise how you used this? I cannot use transpose because my results are over the columns limit.... Thanks 🙂
Worked like a charm, thank you very very much. Keep those helpful videos coming up.
Glad it helped
Hello Leila. Thank for this awesome video. Unlike how you are able to automatically update the dropdown options in realtime (when you type and click the data validation arrow), I have to type few letters, then press enter (which goes to next row) and then come back to the cell where partial letters are typed to see the filtered list. What settings do i need to update realtime like yours
This works nicely. Like Bethu, the list does not generate once i type in a few letters; i have to leave the cell and return before the cell generates list. Other than that it works exactly like you describe.
I should restate prior post. When i enter a few letters, the dynamic prep range is created however, when i click the dropdown I get the full list. If i leave the cell and return the dropdown matches the filtered dynamic list. How can I get the dropdown to sync with the dynamic filtered list w/o leaving the cell?
Question/suggestion... 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 entries (only one matching typed letters)?
You need some VBA code to handle this! Without VBA I have no idea!
You might want to try this if you have fewer data. Let's say less than a hundred
first please watch this trick:
th-cam.com/video/PVEzHbdHf1Y/w-d-xo.html
second, create your list and then hide them
you will see the sugestion / autocomplete based on what you are typing
Hello dear
this is Safiullah Sadiqi from Afghanistan , I always watch your videos and download them
your videos are very profitable , I really learned excel from your videos , a special thanks from my side for you
I use data validation in each row of my excel database , I have a sheet for my chart of accounts and one sheet like journal entry , when i record some data and by name of one accounts if I don't remember that name its doesn't allow me to continue , if is available tell me how i can search and find the list of that accent that i enter , means when i enter the first alphabeted of some name its show us like a list and I select the name of I want
Super helpful video. Great Job Leila ! Thank you very much.
If you came to this video after watching the original video (Searchable Drop Down List in Excel (Very Easy with FILTER Function), you can skip to 5:36 in this one, as up to that point, it's just an intro and recap of the previous video.
Dear Leila, first of all I would like to thank you for the great work. Your videos are fantastic.
The current solution works very well but I'm wondering what I can do to keep the file size reasonable when I would like to use this solution for 1k to 5k rows?
Many thanks
Mounir
Have the same question. waiting for someone to response this.
Maybe we’ll find other method
yeah 1k row give me 100MB xlsx so no usable unfortunately :(
@@YouRQ7XL See the answer above where they used an =IF($B5="","", Transpose(Sort(Filter(Search....... ) Seems like a quick solution.
EXCELent performance…. incredible results and miracle of Excel...Dynamic array functions are useful getting these results.
Thanks Leila and Microsoft Excel team 👍✌
Excellent presentation
Thank you! Cheers!
Is there nothing FILTER can't do?
Agree - FILTER is the Best!
@@LeilaGharani I can not find Filter function
Thank you so much .. Amazing demonstration and very easy to apply .. Saved me from a lot of headache.
You are welcome!
This was so helpful! Thank you kindly.
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?
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
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.
You....I send you so much love from Cameroon... appreciate what you're doing
Thank you for this video! Very useful for something I'm working on for work.
Excel has become really efficient since 2018! Very fast improvement!
That's true. Dynamic Arrays was a big step in the right direction.
Amazing solution. Your are a genius. Can I ask, is there a way of getting those transposed prepared lists as shown at 8:39 to spill down automatically depending on your table size. I see you selected how many rows you had (something like 15 entries in your case). My table that I need the searchable dropdown list for needs to run for a whole financial year, so there will be an unknown number of rows numbering in the hundreds. Ideally I would like the preparation lists to create themselves as new rows are added to the table, rather than someone having to manually copy down the preparation list as new data rows are added.
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.
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!!
Very useful information. You are the best....
I Love your Tutorials, They made my work to function so Smooth. THANKS A LOT !!!!
Great to hear!
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?
Leila, you are amazing as usual.
nice methodical explanation. Very easy to follow. Thanks!
Brilliant, very good explanation. I was looking for this long time ago, many thanks.🌺🌹
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. 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.
thanks a lot ma'm for the solution , of this problem i was looking for a long time. great
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.
Thanks Leila for this great solution!
very nice👍, thanks for sharing
Thanks for visiting
At last I have a solution for my problem. Thank you very much
thank you so much, i was looking for this formal
SUCH A LIFE SAVER
Leila ... this is an amazing technique ... thank you very much ... i learned alot today just from one video
Glad it was helpful!
Hi, I really like how you explain each step. I do have a question about the 2nd column, what change would I need to make to the formula in a situation where the first column has 2 or 3 items in the 2nd column (cascading dropdown) . Currently I am getting the first item it reads. Hoping you can help with me with this.
Hi Miss Leila, thank you for this technique. I already use and it works. It's just that it's not working if I protect sheet. I uncheck the cells but the arrow is still not clickable. Do you have solution for this? Thank you so much
simple question, I have this all set up and it works just fine except when I type a few letters for my list selection it doesn't auto update the list unless I hit enter and I don't see you having to do that in your videos. So if I type "to" and then click on the drop down list icon I will only get my filtered list if I hit enter or select a different cell and then click the icon.
Thankyou very much for sharing and teaching.
You are very welcome
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
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
Thank you Leila for your immediate response. Actually I am having the monthly subscription pack for Office 365 Personal version. Kindly clarify
Amazing!! Can't express my appreciation for this video!
Glad you liked it!
Dear Leila, would there be a way to make a searchable dependable drop-down list. I've got 20 column's and 2000 rows of nutrition data. I've been experimenting but can't seem to figure it out, maybe other people also have this problem
Is there a way, either with or without VBA, for the list to dropdown as you start typing?
first of all I thank you for your gracious work it is really helpful and I just want to ask you about some of these functions which are not available in my copy of office 19 so I was wondering how to get this in office 19 since I don't have Office 365
You can't. The new functions will not come to Excel 2019.
@@LeilaGharani thanks for your concern
Thanks a lot Leila! That is a very informative video as usual. I have learnt a lot from your Udemy courses.
Is there any method to make dynamic searchable lists from source in multiple sheets using INDIRECT in Data Validation? I tried using INDIRECT with # inside brackets, but it doesn't work.
Your videos are great and so helpful and well explained
Glad you think so Jamie!
Love the new intro... Very professional.
Thank you!
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!!!
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?
Hi Leila, thank you for the great video. A few questions, I tried to use it, but when I type in the target cell for the main formula to work, it didn't update realtime like in this video.
which means I have to enter first (automatically exit the cell), before the content from the dropdown list is updated (so I have to reselect the dropdown list after pressing enter).
Any idea why and how to fix this?
second question, can I use this with a large number of rows? will it be efficient?
Thank you
I am having the same problem
Same here, please help on this
very helpful,.
hello leila, I have a question, how do you do that 2:43 just once enter the cell below. Filled with the same formula and does not change.
I'm almost desperate to find a way on Google, please reply to my comments as soon as possible.
sorry for my bad english
That’s automatic because it’s a spilled range. Sheets has the same features if you use functions that spill like filter or sort functions. But because Sheets doesn’t have table referencing you can’t get the same effect as shown here. You have to pull the formula down.
Thank you so much for replying to my comment Leila ,. but I am still not satisfied, because I want to know how, my Excel can also be like that. I Am Waiting For The Next Video ,. sorry "I'm a Beginner"
Is it possible to create multiple dropdown list in official excel table. To create dropdown list automatically when new row add in that table just lite formula is automatically create. Thank you for your sharing.
Liking in advance. Will watch later in the evening. Thank you Leila, always.
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?
it works and it's great
But there is a problem, I have much longer list than yours and my excel file become very very slow, suppose it's because of lots of calculation steps.
Did you find another way to multiply the SEARCHABLE Drop-Down list or this is the latest idea you had ?
thank you
Thanks so much Leila you are very helpful and self challenging
You are the best 😘😘😘. Exactly what I have been looking for. You just gave me a good night rest.
for loong input columns , just retrun last cell ( cour current input) and feed it into your data validation prep formula...
1. Sheet 1 is master sheet of Raw materials and its quantities
2. Sheet 2 is a formulation sheet, Having searchable Dropdown list of raw materials present in sheet 1.
3. My requirement is if I add a quantity of a product in sheet 2, then that quantity should be deducted from sheet 1 quantities. Effectively reflecting the remaining quantity of raw material remaining.
I really liked your way of explaining, the searchable drop down list is working well,now I have following questions please reply asap
Hi Thank you very much for sharing this video,
Have a concern here . If we use this searchable drop down list formula the excel size increases a lot. is there a solution for that
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.