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.
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!
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 :)
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"),"").
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.
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
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.
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.
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%.
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. :)
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?
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 ?
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.
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!
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/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
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.
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
Most thanks sir, continues searching of 24 hrs, I could find you great video. most thanks sir again
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!
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!
That was exceptional, how I've never seem your videos before is beyond me, +1 subscribe
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
You nailed it! Thank you. I'm definitely going to watch more of your videos contents
Awesome! Thank you!
Very smart trick Nabil 👌 👍 👏 I always enjoy watching yout tutorials
Many many thanks
Excellent Nabil.. ألف شكر.. Best regards and stay safe. Salim
Thank you my friend... Take good care of yourself.... The Best Is Yet To Come
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
An intelligent solution we expect you. 👍👍👍
Thank you my friend
Thank you very much! That what I was looking for.
Very Good Bro Keep it up it help me alot......Please Also Add How we will search in Multiple sheets
Thanks a lot I solve a big headaches
Happy to help
Well explained.. it's very useful to me... Thanks sir..
very useful and highly flexible trick revealed and very well explained Thanks Sir for your brillancy shared
Thank you very much.
Really neat! Kudos!
Thank you
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"),"").
Thank you for this amazing tutorial.
You are welcome
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
Awesome tutorial as usual my friend.
Thank you Yasser... Haven't seen you for a while... be Safe and Healthy
Great!
Sir i want the searchable cell in another sheet
how i add sheet ref in =cell(address)
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
Really awesome..
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
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
You are Amazing because you Also Provide Xlsx File which i run in my andriod mobile also
Hi Nabil, please explain how to add more searchable names in the list. I could make it. It doesn't see added names below.
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.
Amazing
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
شكرا جزيلا
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
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
good method sir,thanks
but if we make a dropdown on another sheet, this method does not work. pl help.
Thanks
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.
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 ""
Bravo^2
Thank You ^ 5
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