Create a Searchable Drop Down List in Excel
HTML-код
- Опубликовано: 4 сен 2018
- A Drop Down List is extremely useful in Excel. However, If we have hundreds of values it becomes difficult to find the specific value we are looking for. In this tutorial you'll learn how to shrink your list to fewer options by typing few characters and your drop down list will show only options relevant to what you typed.
You can download the exercise file by clicking on the link:
www.amazon.ca/clouddrive/shar...
or
www.amazon.ca/clouddrive/shar...
Don't forget to subscribe to be notified when new videos are released.
![NoCap - Maliboo [Official Music Video]](http://i.ytimg.com/vi/FUV7E0QyniU/mqdefault.jpg)
mind boggling how something so seemingly simple is really challenging to solve, but done and explained in a way that make it possible for a non-expert to try it
i have watched other videos from other "Experts", I must say, you are the best at this. Very easy to understand, you show the steps that can actually are readable. Great video. Thank you so much. David
Explanation on point. Cant get better than this
This is the best explanation that I ever sow. Really attractive the way u doing it. Thanks a lot. Good luck...!
Thanks as always for your great contribution for the excel community! regards from Uruguay Amigo!!!
I find your explanations amazingly neat. Easy to understand for a dummy like me! Keep the great work up.
Thank you for such motivating comment.
I didn't know that the functions Rows and Max can be used in that way. It is amaizing. Your explanaition is easy and clear. I'm sure to use this drop down list in my Excel files. Thank you for the video.
You are Amazing, I checked lot of videos on searchable drop down in excel.
Yours is the best, I must admit.
Keep up the good work.
I'm using your method in my office work.
A very big THANK YOU!!!.
Glad it helped!
here is a very precious "Thank You " tutorial for you:
ruclips.net/video/e2-uc3nOKlE/видео.html
Best...
Nabil
sharing your knowledge with us is highly appreciated. keep up the excellent teaching method...
Thank you for being a loyal subscriber
Very well explained tutorial..I like the fact you broke it down into little steps which were easy to follow along...Well done and instantly subscribed
Thank you for the motivating comment
So useful and explained in such an easy way. Sir Thank you so much. I am a fan of you. I have seen several videos on this topic. Yours is superb.
Just what ive been looking for! Thanks for this. Clearly explained. Amazing presentation .
Glad it was helpful!
Don't miss part 2:
ruclips.net/video/e2-uc3nOKlE/видео.html
Very well explained sir. Quite helpful shortcuts also. Appreciated
thanks sir jan.. naa jud ko natun an.. review lang tomorrow.. God bless..
Excellent. Well explained. Thanks a lot. Expecting some more from you.
Thank you for the motivating comment. The best is yet to come. Stay tuned
Fantastic! Very clear and easy to follow
Thank You
Thanks so much for your great efforts, it's very helpful video.
Thank YOU for the feedback.
WOW you are a good teacher.
EDIT You are a very very good teacher.
Thank you
Thank you for sharing this! It helps a lot! God bless
Glad it was helpful!
Yesterday I've came across your channel and felt a pleasant surprise that your way of explanation as well as visualisation and selection of attractive colours is unique and I found you among the top trainers of Excel. Keep it up. May Allah bless you a lot.
hello my friend,
I just logged on to my computer to check my mail (it's the weekend, I take a break) and I got a notification about your comment so, I switched to RUclips and read it... You Made my day... Thank you
and upon reading it... I decided to post a new video: TODAY... Stay Tuned
Did you subscribe??
@@Officeinstructor Your response is another pleasant surprise for me. I've watched your first video "Excel in capsule Ep. 5" and subscribed your channel without loss of time and a long list of your videos is in front of me to be watched plus the new one which you have told today. Almighty Allah bless you a lot.
Excellent tutorial, thank you very much indeed. it would be great if you could show an example of INDIRECT(CELL("ADDRESS")) to be used in a searchable drop down list!
will co
Your videos are very didatic! Great job!
Thank you Ronaldo for being a loyal subscriber.
Mr Nabeel good video, very well explained
Thank you
Thank You Luke
superb explanation. Very Useful. thank you
You are welcome
Excellent work boss 😃😃😃
Great Video Sir really appreciate it i also saw other videos from other great teachers like you to figure this out it does the same thing lol, I saw your second video on creating the dropdown list on multiple cells like one list below the other, the thing is when you type on the first cell and you click on any name on that dropdown list its good but when you go to the cell below the dropdown list disappears you only see the previous the name you clicked on the first cell its like stuck with the data of the first cell you clicked on. Both videos the list works but only on one cell i know the trick is when you get to the next cell to type your next name or whatever it is in on your dropdown list click the backspace button and the dropdown list works perfect again where ever you have your list no need for the cell address add on the formula. I have no idea why you cant get this dropdown list again without clicking the backspace button it just refuses to work without hitting backspace on your next item to add on your list or ,maybe that's the way it suppose to work now lol. Keep up the great work you and other teachers like you are doing may God Always BLESS people like you out there
What a pleasure to have nice people like you and Excel enthusiast subscribe to my channel.
Great video. Amazing. You really help me out.
Glad I could help!
Watch the amazing Part 2
www.linkedin.com/feed/update/urn:li:activity:6717402872633442304/
Thank you for the explanations. Its very easy to understand. Question, How do I apply this to many cells in the column? It seem to work on one particular cell (A1). Thanks in advance
thank you so much, this is awesome
Cool! Thank you!
thank you for well explained tutorial, followed it and work very well on text, question if I could, how to make it work if your lookup list is Alpha-numerical? thank you
Thanks sir
Very helpful in day to day life of data form
Most welcome
Thank you for the excellent tutorial. How does one make multiple selections within a searchable drop down cell?
Requires VBA code. I explain that in my Book "Data Validation ... A Back Door To Master Excel"
available on my website www.OfficeInstructor.com
Thanks a lot, I search a lot for this function and can't find because i have a long list names and want to search at 8 different sheets
:) Very very nice. Thx alot.
No Problem... You're welcome
Fantastic explanation. I appreciate you taking the time to articulate the formula logic. As a layman, that is so helpful. Is there a way to enhance the final data validation tab (A1) so that as I type, the drop down opens and shows the available matches from the desired range? I want it to do exactly what you described, but I want to see the options without having to click on the dropdown list to see what was available.
Thanks again for your excellent demonstration.
Glad it was helpful!
Don'tmiss part 2:
ruclips.net/video/e2-uc3nOKlE/видео.html
@@OfficeinstructorI will do that. Also, my dropdown list shows duplicates. Did I do something wrong? I have a VBA code to eliminate duplicates in a list but is there a way to fix that in your approach?
Hallo Nabil
I'm suggesting this equation in column (H)
=IF(ROWS($H$1:H1)>MAX($E$2:$E$135),"",VLOOKUP(ROWS($H$1:H1),$E$2:$F$135,0))
Because
The IFERROR Function Force the program to calculate the vlookup and if it finds an error it Gives an empty cell
So in the proposed Equation , Excel in this case stop the calculation of the vlookup function when The number of rows exceeds the Max of $E$2:$E$135 witch can be 5,10,... or any number else and the # N/A # Disappears
I agree
Thank you. good work. how can we use this option in continuous cells(rows)?
Thank you for the nice comment, and yes you can create the functionality in any cell or range but replace the $A$1 in the search function by:
CELL("address")
Sir u have explained in a very clear manner. Can u make a video how to create a dependent searchable droo down list with four or more columns. Thank you.
I have a Free Master Class on Dependent Data Validation on my RUclips Channel. Check this AMAZING video
Sir u mean the around 54 minute video uploaded by 3weeks ago. I just started watching it. Hooe it will help me. Thank you once again.
@@abdulsalam935 Yes
Thanks! its very helpful! I want to know, If I chose name in dropdown it should display only my ID instead of Name in the same cell.Is it possible to do? please give me soultion for it.
Yes it is possible.
Watch this tutorial
ruclips.net/video/0MuQfUJbycQ/видео.html
TREMENDOUS VIDEO AND VERY EASY TO FOLLOW! my questions is i have copied everything exactly and it work perfectly HOWEVER when i go to copy & paste that cell down the entire spreadsheet (on the same page) i lose the functionality of typing in searches as it only returns the results from the original "master cell" used in the beginning. How do make each new cell reference the entire list again? again many thanks, and it was an expertly explained and the downloadable sheet was awesome - rp
Replace the cell having the search term (A1 in the example) by a more general function: CELL("address")
this will allow you to get the same functionality in other cells
@@Officeinstructor i;m tryin this but seems not work or maybe i'm not this expert . can u help us to make the instruction on the videooo .... thanks before .. anyway the way you create this tutorial is different the other and i think this the easiest and clearest one ...
If u want to copy and paste in down or entire sheet, first you have use data validation then make list from H column example H2:H10, secondly use cell content formula in A1 cell. Hope it's work on all cell
You have to click backspace button each time unfortunately on a new cell the entire list appears again the formula works greats
Good post
Nice post
I greatly enjoyed your presentation and had no trouble getting the desired results as long as everything was on the same worksheet. How can you have the VLookup cell on sheet 1 and all of your data on sheet 2?
in such case you need to precede the cell reference for the lookup value with:
Sheet2!
@@Officeinstructor Hi, could you please state the formula for that? Thanks!
hi sir 😊 it's good 😊 huge thanks ☺️ this possible on excel 2016? pls☺️
Great work thanks, how can i copy the searchable drop down list down, i need to use it to create a form for data entry.
I have more than one searchable drop down list in one of my workbooks. If You have more than one drop down list You can't use just one source. I overcome this problem by simply copying source list and I adjust formulas for every drop down list.
Hi Nabil, I love your tutorial so much,I got practice very smoothly with it, but it was not define name when I copy cell A1 to another sheet, pls show me how can I do??
Will create a video on this topic and notify you then
I am looking forward to hear from you soon. thanks
I am lost when i use the countif function as shown on the video the: Offset($H$2,0,0,countif(h:h,"?*"),1)
i see your example resulted in a single cell with the name dominic micheal. when i do this same exact formula(using a list of my own) i have a duplicate of the entire list that comes up in the search vs a single box like you have in your video
thank you so much!
i have tried your method but i need this searchable list for an entire column, not a single cell. what should i do?
Here is EXACTLY what you are looking for.
I created another tutorial that complements the one you watched. Here it is:
ruclips.net/video/e2-uc3nOKlE/видео.html
Let me know what you think.
Thank you so much
Just click backspace on the next cell it works perfect again lol
What if i wanted to use the same list for several cells below?
The search for me only works on the first cell.
Going to the cell below would only show the value selected above and if nothing is selected it would only show the dropdown list without the search function!
I hope you get me and help me.
Wow, this is great stuff! Thanks Nabil! How can I download the file?
www.amazon.ca/clouddrive/share/7cb7Cpgba8vWzRnoVoKoEivSqxb6uuHz7Y4tabBZ9Ua/2BrRMsggTzGw2HJy23WNqA?_encoding=UTF8&*Version*=1&*entries*=0&mgh=1
@@Officeinstructor Hi Nabil... it would seem that this file download has expired... can we still download the file? Thanks
@@markwhitington4252 try this link
www.amazon.ca/clouddrive/share/6MAMOUY4uHsQIlgN2XdQMXOyayBwuI2YfhbAvuHlRBN
let me know if it works to add it to the video description
@@Officeinstructor Thank you Nabil... the provided link works well :-)
@@markwhitington4252 Thank you for the update... Best of luck
bravissimo
Excellent video, but I have a question. If your initial list contained duplicate names, how would you discriminate between them?
Remove Duplicate first
@@Officeinstructor Thanks for the reply, but when I say duplicate I refer to different people with the same name. None of them can be removed. This can happen a lot in my country particularly if the list is extensive. My workaround consists of concatenating dates of birth with surnames, but it is cumbersome.
How to modify it to open the dropdown automatically and show suggestions while typing instead of clicking the arrow each time.
You are too demanding my friend
what if i want to make multipe searchabe dropdown list in same sheet ??
Replace Cell A1 in the first formula by
INDIRECT(CELL("address"))
How it will be work multiple cells in a column
Read previous comments. I answered this question before
Quick questions,
I have a list of 100 people right now, but in the future, I will have more people. As time goes by, I need to add more people into my list or adjust something in my list, so how do I keep the list update without having to go over the entire process again? The second question is how to create this in multiple cells?
P/S: I copied the process that you did, but if I add more people into my list, the Excel would not update it. Thank you very much
There are many ways of creating an "Expandable Range" such as converting your source list into a table first then refer to it by it's table name, you can also refer to the source list by using an Offset Function...
Sir,how to add extra name which is not in the initial list?
How to apply it in other sheet? Can it works?
Yes it is possible!
replace $A$1 by
Cell(address)
Boss, can u make that droplist expand without click it, just tiping in the cell?
In VBA we can execute commands by hovering but I do not see it useful in this scenario because it brings confusion as well.
@@Officeinstructor I don`t need the VBA just a droplist that can expand when i`m typing in that cell. The VBA is used for combo box and i don`t need this box just the droplist because i have 700 hundred rows to fill with some name companies from another sheet. Thats why i need the expanded droplist when i`m typing in the cells. And after this i have to import the file in an accounting program ....so only droplist from cells will be recognizable. NO VBA just a simple droplist that expands when i`m typing in that cell and shows me the information i need from the other sheet. I looked 2 days, i think at every single video and no one did this before, so just a simple drop list that expands. Thank you!
How to filter data from 'F' coloum by created searchable drop-down list. Please guide me.
Can i put drop down list in sheet ???? And i make my formula in another sheet ??
Sure you can Sameh.
Watch part 2 of this tutorial
ruclips.net/video/e2-uc3nOKlE/видео.html
Sir, can we drag this to below rows?
Not with this specific method. I have another tutorial on RUclips where I create a Searchable Drop List ANYWHERE in the sheet and can be dragged.
Check this:
main list in column D
at E1: =FILTER(D:D,ISNUMBER(SEARCH(INDIRECT(CELL("ADDRESS")),D:D)),"Last entry not found")
at Define Name:
=OFFSET($E$1,0,0,COUNTIF($E:$E,"?*"))
Thank you for sharing your solution, but note that when I created this tutorial 3 years ago dynamic array functions were not yet introduced!
So if you check my videos you find many tutorials on creating searchable drop list in ANY cell or range using extremely powerful techniques.
hillo sir can i ask?
This not worked in office 2016 and i have excel table please try to redefine again with excel table again
I am recording this video in Excel 2016...It worked with 5000 viewers, then you may have missed a small step. You need to watch the video again.
I followed every step but I'm having trouble. Instead of using words I'm using numbers and if I type 2 it will give me anything that has 2 . like 12 22 32......Is there a way to fix this problem.
the fourth argument of the VLOOKUP must be FALSE (=Exact) for the function to work properly with your numbers
@@Officeinstructor so what do I type in after =exact
You don't type "Exact"... you type "False" which means "Exact"... I guess you need first to learn about the basic functionality of a VLOOKUP function.
@@Officeinstructor you probably right. Also what if it's in multiple columns or rows?
@@Dopeboyz789 in this case you need to watch this tutorial
ruclips.net/video/9yhbh6l_rag/видео.html
how exend this searchable to entire col
Replace cell $A$1 in the search function by
INDIRECT(CELL("ADDRESS"))
Hit the backspace button each time on a new cell before you type again and list appears again nothing wrong with the formula not sure why you must hit the backspace button in order for it to work it just works lol
It should be auto populated and auto completed, there should be no need of clicking on arrow of drop down list than it will become real productive feature.