How to Create a Dynamic Searchable Drop Down List in Excel
ฝัง
- เผยแพร่เมื่อ 28 มิ.ย. 2024
- How do I create a multiple searchable dynamic drop down list in excel?
There are three parts here. Data validation drop down list should work on multiple cells. It should be searchable. It should be Dynamic. In this tutorial I’ll show you how you can do all these at once. Most importantly I’ll be using different formula and functions only. No VBA required. You can use this to any excel versions from Excel 2010 to Office 365. There are a few things I’ve used in this tutorial. If you want you can learn them before starting.
Named Range: • What is Named Range & ...
Dynamic Named Range: • How to Create Dynamic ...
Drop Down list using Named range: • How to Create Drop dow...
Dynamic Dropdown List: • How to Update Items in...
I’ve also Used different for and they are as follows:
Search Function: • How to Use SEARCH Func...
IsNumber Function: Video will be uploaded soon
IF Function: • How to Use IF Function...
Max Function: • How to Use MAX Functio...
Vlookup Function: • How to Use VLOOKUP Fun...
IFERROR Function: • How to Use IFERROR Fun...
Offset Function: Video Will be Uploaded Soon
Rows Function: Video Will be Uploaded Soon
Count IF Function: • How to Use COUNTIF Fun...
Now that you learned how to use these functions and technique let’s follow the procedure to create searchable dynamic dropdown list that can be used on multiple places.
First create a table with your data. First column will be helper column and the second column will be the data you need to include inside your dropdown list. You can do this without tables but then your dropdown list will not work properly if you add new value. In this way i was able to create a 100% Dynamic, searchable drop-down list in excel.
Now you need to place the formula inside the first column. The formula I used in the video is as follows:
=IF (ISNUMBER (SEARCH ($F$1, [@Names])), MAX ($A$1: A1) +1,0)
Here @Names indicates each cell of the name’s column from the table column. This formula will create a unique serial number each time if matches the value from Cell F1. Change the cells according to your need.
Now we need to extract the list based on our search. As our helper column is already sorting the values that matches the search, we need to just take it from there and fill it in a new column. In my case I’ve used column H. The formula I used here is as follows:
=IFERROR (VLOOKUP (ROWS ($H$2: $H2), Name_List,2, FALSE),"")
That will extract the value perfectly. Now our search feature is ready. Now I need to create a dynamic named range here and feed this search result inside data validation drop down list. Click on the formula bar. Click on the name manager and click new. Give a name for your dynamic list. And place the below formula inside the reference.
=OFFSET (Backend! $H$2,,,COUNTIF(Backend! $H$2: $H$500,”?*"))
Now the last part. Write the Cell Function in Cell F1
=Cell (“Contents”)
Now create your dynamic dropdown list and from the error alert tab uncheck the option “Show error alert after invalid data is entered”.
Done. You’ve just created multiple searchable dynamic dropdown list in excel using formula.
#DropDownList #Searchable #Dynamic
Thanks for watching.
-------------------------------------------------------------------------------------------------------------
Support the channel with as low as $5
/ excel10tutorial
-------------------------------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
goo.gl/uL8fqQ
Here goes the most recent video of the channel:
bit.ly/2UngIwS
Playlists:
Advance Excel Tutorial: goo.gl/ExYy7v
Excel Tutorial for Beginners: goo.gl/UDrDcA
Excel Case: goo.gl/xiP3tv
Combine Workbook & Worksheets: bit.ly/2Tpf7DB
All About Comments in Excel: bit.ly/excelcomments
Excel VBA Programming Course: bit.ly/excelvbacourse
Social media:
Facebook: / excel10tutorial
Twitter: / excel10tutorial
Blogger: excel10tutorial.blogspot.com
Tumblr: / excel10tutorial
Instagram: / excel_10_tutorial
Hubpages: hubpages.com/@excel10tutorial
Quora: bit.ly/3bxB8JG
You sir, are a GENIUS! Best solution yet for those without access to Dynamic Arrays in Excel 365. That too you have implemented it without any VBA/Macro or other such advanced functions that are difficult for non-experts. Beautifully explained and I just had to follow you step by step to get the outcome.
Thank you!
Wow, thanks!
Great video. I had tried several different methods with no luck. This solved my problem. Thanks for sharing!
Glad it helped! Please subscribe and share
Love You Dude.. Such a useful Tip. I was searching this for 3 days...
Glad I could help. Please subscribe and be with us.
So much helpful, I have been searching for this my whole day, thank you
Glad I could help!
Thank you for the videos. You deserve a big load of respect.
Thanks a million
Thank you so much! you are a life saver. Been looking for this for a very long time.
Glad I could help!
Good day, sir.
followed your instructions and worked well.
Thank you, sir; you are a genius!
You're most welcome. Please subscribe and be in touch
broo.. u are awesomeeee !!!!! thanks a lot..
You're welcome!
Thank you so much for such a wonderful video.
You're most welcome. Please subscribe and share.
Wonderful application and great tutorial.
Glad you think so!
thank you for sharing this video very infromative and it ie easy to follow how to do dynamic searcheable dropdown list especially i am using excel with no filter in formula box.
Glad you enjoyed it! Please subscribe and be with us.
Hi sir was very very useful. Very informative. Thanks. It had the features of searchable, dynamic and multi .in others it was not multiple rows .Thanks
So nice of you
Great Work Sir.
Thank you! Cheers!
Thank you so much for this video
It helps me a lot
You're most welcome! Please subscribe and share.
Really thank you for your effort we really appreciate it. I subscribed to your channel
Thanks for the sub!
you r simply great .... thank u
You're welcome. Please subscribe and share the video.
very good, i was looking for writing my password DB, then have a search on a criteria like account name (unique) and then extract all relevant infor around this search. Worked well.
Glad I could help!
thanks for useful information
So nice of you. Please subscribe and share
Thank you sir...this is great
Glad you liked it
VERY HELPFUL ...TQ
Happy to help
excellent sir excellent. no words
Thanks.
Sir as you are using office 360 you dont take to use search formula. But can you tell for excel 21 how I can add search formula to the formula in the video. I need searchable, dependable and dynamic dropdown list for my balance sheet. Thank you
THANK YOU SIR..... THANK YOU SIR, AGAIN....
So nice of you. Please subscribe and be with us
This the close to solution what i got. Thank you. Is there a way to list all names before typeing?
A fullstop before all names. So when you need all name list, you just type fullstop.
A very useful video, thanks Kazi.While I was testing it I wasn't sure about the user experience of trying to use the lookup list without entering anything in the search box.
ie if they just click on the scroll button on the search field then there is nothing in the list.
So I modified my version of this formula
=IFERROR(VLOOKUP(ROWS($J$4:J4),CountryOfOrigin,2,FALSE),"")
I changed it to this
=IFERROR(IF(MAX(CountryOfOrigin[Helper])=0,INDEX(CountryOfOrigin[Country],ROWS($J$4:J4)),VLOOKUP(ROWS($J$4:J4),CountryOfOrigin,2,FALSE)),"")
So basically if there isn't a value in the helper column then it displays the full original list.
Cheers, Richard
Thanks a million for your solution. I'm sure it will help a lot of people. Glad to have you here.
thank you !
You're welcome!
Appreciated.
Thanks a million. Please subscribe and share.
Great video! Could you kindly please let me know what your circular formulae settings are? (Max iterations and max change). Thank you!
For this tutorial? Usually, I just enable iteration and used the default option.
Excellent
Thanks a lot.
thanks
You're welcome! Please subscribe and be with us.
u r awesome ^^
Thanks for the comment. Please subscribe and be with us.
Fantabulous...
Felt that you are explaining in front of me.
Using the filter function this helped, but unfortunately, it is available only with Office 365.
Your tutorial helps me to find admission numbers through a searchable drop-down list...
Great to hear!
Very awesome. Is there any way I can get sample of that file? Thank you so much.
Follow the instruction and you'll be able to do it easily.
Brilliant
Thanks man.
Thank you very much. Very nicely explained. Can the list be sorted in alphabetical order? We have to give a space and then only all the items get displayed. While pressing the drop down without typing, no items are displayed. Is there a way to get over this.
You have to search first
Hi there, I have a question! Isn't this method going to trigger the creation of the "filter" each time you write anything in excel? I can see that this would be amazing a small file but have you tested this on a massive file?
No i haven't. Let me know the result.
Hi. How to make it work if new row with data was inserted to the table? Lets say in a middle of the table. It dosn't show up in searchable dropdown menu. Helper row is missing formula then.
Place new value at the nd of the table.
How about if I type multiple letters instead of only one letter when searching?
Of course. You can do that.
wonderful, your video helps, keep up the good videos :)
Thanks for the comment. Please subscribe and share.
thanks tooooooo
Always welcome
can you give me a link to reducing data validation drop-down list tutorial
Check this: th-cam.com/video/EVGCPZFwv4w/w-d-xo.html
How did you change B4 to @Names
Please check these videos on named ranges.
th-cam.com/video/ax87ihcfH3E/w-d-xo.html
th-cam.com/video/mLKT0DcVBWo/w-d-xo.html
th-cam.com/video/5brNPIunUvY/w-d-xo.html
Is it works in any M.S version ❓
Haven't tried on excel 2007, so don't know about that. But it should work from excel 2010 to above
Sir, where is the formula, which you said, will be shared below. For lay people to follow this rather complicated formula, is bound to send in error. Please provide the formula in this column
In the video description.
When i add the Max formula with +1 the excel hangs
Which version are you using. I'm not sure why it hangs at your end. Mine works fine.
How is the logic of max formula,in fact I know but here I didn’t understand the function of it,also my formula doesnt work,always show 1 value,in fact max formula reveals the maximum of different numbers,but here it sums and its cut by when he face the value of zero? You said its not about the version either but my formula doesnt work
It will work. Try again. Try to do it step by step. There are lots of steps here. Make sure you do it just like i did.
HELLO SIR, HOW CAN DOWNLOAD THIS FILE PLZ
Check description
Hello, one question, does this also work with Excel 365?
365 doesn't need this complex option. you can do this using search and filter function. That's much easier.
Hello, can you give a short example@@Excel10tutorial
Just create a dropdown list. Click on Data - Data Validation - Select List - Select the range where you have dropdown value. In the dropdown cell if you write something it should be searchable in 365. You don't need anything.
I was on fire watching your video, but when I put in the OFFSET Function, the formula Spills Over. Result, the Formula Name is not accepted by the Data Validation List...
Waiting for Office 365 to come with Multiple Search Drop Down Lists as standard, now...
Please try again. it should work. There is a lot in this video. So make sure you follow each step properly.
@@Excel10tutorial Okay, I will try again... It IS difficult to get it right the first time, I admit!
Will update you here!
Too Fast.. :( can't understand the formula.
So sorry for that. For me this is a long video. I tried to be fast. But if you need the formula i've added it on the description. you'll find the formula and instruction there. If you need any help, i'm here.