Multiple Searchable Drop Lists …ANYWHERE in the Sheet? Are you kidding me?
HTML-код
- Опубликовано: 23 июл 2024
- There are different ways to create a Single SEARCHABLE Drop Down List (a Data Validation List) in Excel…All these techniques enable you to type few characters and the content of the drop down list shrink accordingly, making it easier to find the desired option.
HOWEVER, … all the techniques allow you to create a SINGLE Data Validation list, in a Single cell.
Some time ago, I posted a Tutorial on How to create a searchable Drop-Down list in a single cell, You can watch this tutorial by clicking on the link in the description below.
• Create a Searchable Dr...
I then received a specific question so many times:
How can we get the same functionality in MULTIPLE cells Anywhere in the worksheet?
In this tutorial, I modify one of the 9 functions I created in the previous tutorial … I encourage you to watch that tutorial and then learn how to expand the Searchable functionality of your data validation to Multiple Cells ANYWHERE in your worksheet.
You can download the Exercise File and Follow along, by clicking on the Link here below:
www.amazon.ca/clouddrive/shar...
To read my Step by Step Blog articles or Register to one of my Free Webinars, visit my blogsite:
www.OfficeInstructor.com
Follow me on Facebook: www.Facebook.com/OfficeInstructor
To book a Corporate Training or take a Microsoft Office Specialist Exam, visit:
www.OfficeInstructor.ca
Finally, if you find value in my tutorials, you can support my Channel by hitting the Like button, Sharing, dropping a comment and subscribe, to be notified when new tutorials are released…
The Best Is Yet To Come.
![Sean Rii, Karyon, Sharzkii - Taungule [My Love] (Official Music Video)](http://i.ytimg.com/vi/eJoMuypbSzQ/mqdefault.jpg)
Nabil this was crazy amazing baraak Allaho fik and stay safe
Thank you for the nice comment....Stay Tuned... The Best is Yet To Come
That was exceptional, how I've never seem your videos before is beyond me, +1 subscribe
you guy you are so ginius. i thank you so so so so so much. you have got me out of a deep grave. Thanks
You are amazing!!! I've been looking for this process for quite sometimes along with many, many trial and error. Between your 2 videos, I now have a multiple searchable dropdowns spreadsheet. You have a wonderful day Sir! Thank you so much!
Sir, I am very much delighted after going through your video SEARCHABLE DROPDOWN LIST ANYWHERE. I have confidently created a spread sheet by goind through your video. Once again thank you.
All the best
Excellent tutorial Nabil. Is't always difficult to use the INDIRECT and CELL functions, but with this video all is ok. Thank you!!!
You are welcome!
Hi, Nabil Mourad Bhai you just save our lives. I am searching for many days to find out an solution. Now i got it. Thank you so much.
You nailed it! Thank you. I'm definitely going to watch more of your videos contents
Awesome! Thank you!
Thank you very much! That what I was looking for.
You the best sir.
Most thanks sir, continues searching of 24 hrs, I could find you great video. most thanks sir again
Thanks a lot I solve a big headaches
Happy to help
Thank you so much for creating such a well-explained and easy to follow guide. You've explained every function in detail so that it is intuitive even to beginners like me. You've saved thousands of corporate lives :)
After watching this video... You are not a beginner anymore
Awesome tutorial as usual my friend.
Thank you Yasser... Haven't seen you for a while... be Safe and Healthy
Thank you for this amazing tutorial.
You are welcome
Really neat! Kudos!
Thank you
Excellent Nabil.. ألف شكر.. Best regards and stay safe. Salim
Thank you my friend... Take good care of yourself.... The Best Is Yet To Come
Very smart trick Nabil 👌 👍 👏 I always enjoy watching yout tutorials
Many many thanks
very useful and highly flexible trick revealed and very well explained Thanks Sir for your brillancy shared
Very Good Bro Keep it up it help me alot......Please Also Add How we will search in Multiple sheets
Well explained.. it's very useful to me... Thanks sir..
Thank you very much.
An intelligent solution we expect you. 👍👍👍
Thank you my friend
Really awesome..
شكرا جزيلا
Amazing
Love your videos. Very well explained, easy to follow. How about some VBA for the shelter in place crowd ? Stay safe.
Thank you for the nice comments.... Stay tuned... The Best Is Yet To Come
Helpful, as usual. However, I am trying to do something different. I have made a form and want to select from within the dropdown list (basically simple data validation) However, I want the searchable options to modify the dropdown list within the cell with the data validation. Meaning, If I start typing "mic" the dropdown list would display only the options with "mic" in it and would further shorten the list as I enter more data. Is that possible? It would require the dropdown list to be visible while typing.
Secondly, is it possible to use the search function you described to collapse a set of data by row? When searching, entire rows of data are shown to match the search key.
Thanks
Thank you very much for your help !
It works perfectly untill I put de Cell function in the column E
I have a error message saying "There is one or more circular references where a formula refers to it's own cell either directly or... try to remove..."
In fact I have an error when I try to use the drop down list, there is only one thing I didn't do as you do, my drop down list is not on the same sheet as in your case and I use an xlookup instead of vlookup (but it works with so don't think is the source of the problem). I see you have the same message but it seems to work in your case, so don't know why not in mine. Not sure it's because I try to use the formule in another sheet or it is ?
Or maybe when I create the defind name for the offset formula I need to chose just on sheet or the whole workbook (default option) ?
thank you
Running short of words to aptly describe this innovation and improvization. Thanks once again for this wonderful brain teasing tutorial. Just a small request can you show us how to get specific key word mails from a shared outlook mailbox into an access table.
The Best Is Yet To Come
Great!
Sir i want the searchable cell in another sheet
how i add sheet ref in =cell(address)
You are Amazing because you Also Provide Xlsx File which i run in my andriod mobile also
Thanks so much for this helpful video! I was wondering if there is any VBA code you can use so that the user doesn't have to click enter after typing the key word? so they just type the keyword and click on he dropdown list arrow to view names.
Although I prefer to hit enter, however you can create a Selection Change event that reads: Worksheets(“YourSheetName”).Calculate
Search formula will be triggered whenever a value is input in any of the sheets. If there are large tables, then that would be huge computation overhead. We can limit triggering search only upon updation of specific column. Suppose we want to provide dropdowns in Col A only, then the search should be triggered if the edited column is A. That too can be done by using CELL("col") formula. In your above example only A1 will provide the search criteria, but that cell will be updated only if any cell in Col A is updated. Similary we can set limitation to a particular sheet only. Formula in A1 can be IF(CELL("col")=1,CELL("contents"),"").
Bravo^2
Thank You ^ 5
Dear Sir,
Firstly, thanks for your excellent tutorial, I watched your previous video too.
I just want to know, there are times when I may need to expand the names / add some data to the very first column. How can I do that? Will there be any problem if I add any data to the column?
Did you read my book: "Data Validation...a back door to Master Excel" ??
This is your one stop shop for all kinds of drop lists
Hi Nabil, please explain how to add more searchable names in the list. I could make it. It doesn't see added names below.
Can you post a video about doing this with a structured excel table please? It worked great when it was just unstructured data in a "table" but when formatted as an Excel Structured Table, big problems show up.
Particularly, I want my searchable dropdowns to be within a structured table and I am pulling data from another structured table on a separate sheet.
Is the simple fix to just get rid of the structured tables? Please help. :)
Watch my other videos on Data Validation
Well done, now I like to see it with partnumbers like 123-abc-23.
Your counting with countif "?*" trick won't work properly.
But i got figured out how can be done. I use the max function on your search column (what was the highest number?).
That works 100%.
You will find on my channel another tutorial (Showing 3 Ds) for a SEARCHABLE - Diminishing - Drop List
Hello Nabil, I used this trick on 2 sheets it worked on one of them but didn't work on the second which has a cakcukate and a chnage VBA events. The first sheet didnt have any VBA codes. Do u think the VBA could be the reason ?
Not quite sure my friend... In general, when creating 2 dimensional functions make sure you include the sheet name.
good method sir,thanks
but if we make a dropdown on another sheet, this method does not work. pl help.
Hello I get the same error of your video (i.e. circular error) but I have not selected E1. May you share the file you created here to help people not so proficient like me?
Nabil sir if you shared any video's to any one of your friends through what's app it will impact on our mobile performanc.So please share me your valuable suggestions to me on this issue regards.And how to backup that video.And please respond to my comments.What are steps to be take to improve our mobile gajets.
I do not use Whatsapp... It's not my area of expertise
When I add the indirect(cell("adress)) the numbers incrementor turns zero and so thr Vlookup gives nothing "". I tried it also on your start file and I refresh column H then it gives ""
thanks. but this not work in another sheets or table. its just work with first cell in a table name.
on another sheet not in the sheet that use data, i use funtion cell("adress";tablename[column1]) => its just work with first cell , how to fix this man ? thanks!
damn it. i fixed itttttttttt. its work 100% ok now.
gr8
Thanks
Thanks, I was looking for this solution.
Glad I could help