You are the Yoda of Excel. Thank you for part 1 and part 2 of this solution. I really appreciate your attention to detail! Your solution works across multiple sheets too, so If you place the lists on a separate sheet, you can have multiple "Searchable Drop Downs" on various sheets all referencing one "Lists" sheet (which you can hide). Brilliant!
This has been a godsend for me in setting up multiple projects (from a list of circa 500+ projects, multiple customers, and multiple resources and aiding calculations of resource utilization and revenue generation that much easier. Thanks Neil, top stuff!
Thank you so much Neil. I created a separate data entry sheet and referenced the validation list on a separate sheet. It was a great help especially that the names to choose from are about a thousand names. Cheers!
Thank you so much for putting this together. This info, plus the additional info from part 1, will save me so much time. A quick side note, I started to experience excel not responding shortly after implementing this. But it was do to some of my other sells using index\match of an entire column. When you use reference an entire column ($A:$A) you are asking Excel to look at over a million rows. Keep this in mind if you run into excel not responding.
Great share Neil - The first video was good and then I needed exactly this to finish off the project. So glad I came across the first video. But then the second, as I thought I was stopped in my tracks again. Just wanted to say thank you :-)
Thank you Neil, excellent follow up... I can finish the project now or at least proceed to the next problem (lol). For anyone interested I am using the function to search a list of foods to find their carbohydrate content. I can now enter a food type, ie potato, carrot, chicken etc, and display the total carb content of the meal. I will then use the information to tell me the units of insulin I require to treat my diabetic problem... Thanks once again Neil, for a well made video...
Wow, what a fantastic result, brilliant. Not only cell reference but dynamic lists is new to me and always given me a headache having to use static named lists - really clever.
This was awesome. It really helped with the project I'm working on. I also found, if, in the Data Validation box, you check if the value in the current cell isn't equal to the value in the cell pulling "contents", and if not, have it pull from the full list, it fixes the blank issue For Example, in the Data Validation box of cell D5: =IF($D5$D$2,$G$2#,$I$2#) Now, this can be filled down and if you select any field that doesn't have the same value as the last thing you typed (which is in D2), then it will show the full name list. If, however, you select a name, then go back to the box and pull down the drop-down, you will only see that name. Also, I removed the returning of 0's with =IF(CELL("contents")="","",CELL("contents")), otherwise Null was returning 0
Great videos, just a quick addition that works for me to solve the empty dropdown if nothing is typed in. Within the data validation box, I use the following IF statement: =IF(D5="",G1:G22,OFFSET($I$2,,,COUNTIF($I:$I,"?*"))) In short, if nothing is typed, the full list is shown, however, if you type then the offset rule is triggered. (I used a named range for the full list for ease)
Hi Neil, this is a very clever trick, thank you for sharing. To solve the last problem (5:50) one may do the following: (1) Select cell A1 (this is important) (2) Add the name "validation_list" and make it refer to the formula =IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101) (3) Change the data validation source to the formula =validation_list Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have: (1) changed the COUNTIF() function to the MAX() function (2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list.
That was brilliant, just answered the question I asked earlier. to get around the no return, what I have done is finished my data with a space, so if I include a space I get my full list Thanks again
I also thought about that but i ended up keeping my data as they are and modifying the formula which starts with if(isnumber(search... to something like if(or(cell("contents")=0,isnumber(search.... So far so good. Hope it wont crash
Thanks a lot for your tutorial on this. Helps me a lot.I have multiple drop down list, each one with different reference and the list is long enough to make my excel lagging. Rather than using CELL, i prefer macro.
Just contributing my 2 cents, To limit the search trigger to a specific column and row and to make the whole list visible when no text is entered initially to the dropdown list cells, or when you omit the text in those cells.. =IF(AND(CELL("col")=2, CELL("row")
Absolutely Fantastic! Not only the content, but the style in which you teach us how to become better users. One small suggestion is you may want to copy the contents of the original dropdown and then do a special paste into the target range rather than recreate the data validation in the new range.
I was struggling to understand this and get it to work so ended up playing around with the whole sheet. I ended up adding the cell function into the 'if' in column a so =IF(ISNUMBER(SEARCH(CELL("contents"),B2)),MAX($A$1:A1)+1,0).
If you want to extend this to only trigger the search if the data is entered in a cell from a specific column (even from another sheet) add the following function to the master search cell :=IF(CELL("col")="";CELL(contents);"")Regards,David
i think i got it: =IF(CELL("col",,CELL("contents"),"") i was trying it on the same worksheet but it wasn't working, had to switch worksheet i guess.. also i used a table for mine so it'll auto-add new dropdowns with tab at the end of the table, so mine looks like: =IF(CELL("col",Table2[TEST PARTS]),CELL("contents"),"")
Great video instruction Neil thank you. I have this working across multiple sheets (to populate courses with students names - also fills in additional info in columns from lookup table). My problem is when I protect each worksheet the dropdown doesn't refresh to show the names (only the last cell content entered), if I type part of the name and press enter THEN click the dropdown it works. Anyway to do a refresh on protected sheet?
Dennis - How'd you get it to work on a different sheet? I"m getting an error when trying to make the data validation look at the offset formula from the other sheet. Says I cannot use unions or references in Data Validation formulas.
Great it works A1 but I have a scrip that move things around within the workbook and so the Cell("content") function is called every time and really put a toll on Excel. Is it possible to limit the range of the function of Cell("content") to a specific range. For example if there's a change only within A1:A100 ?
+Neil Firth Thank you for these great tutorial videos, they are very practical. My data entry columns are in another sheet and I cannot seem to generate the dynamic list as intended. Are you able to provide a sample file that contains the data used in the video so I can compare please?
You have literally made my life a living hell.. no wait.. My boss has you have saved me.. I just want to thank you.. Id almost kiss you if I met you.. :D
These two parts have been super helpful! Thank you so much! Question though: Is there a way to either force-select or auto-complete the inputs in Column D? For instance, I want to make sure that the final input is a string that is equal to one of the names listed in Column G--in other words, can we put an error message if the user types Tiger and hits enter--because I want the actual input to be Tiger Woods. (I hope my question is clear).
Hi Neil, Thanks a lot for part one and part two it wors perfectly! I Previously used INDIRECT as part of my drop down list, as the list was dependent on the contents of the cell next to the list. Is there a way of getting these to work together with the search function?
Thank you for posting this video (as well as Pt 1), it was very helpful. Is there any way of using this searchable drop-down list while still utilizing an Error Alert function?
Hi Neil, your solution is very interesting thanks. I was wondering if it is possible to oblige an operator to insert in the cell only the content of the searchable drop down list. thanks massimo
I liked the addition to this video of the =cell("contents") piece as I did want to use the data validation across a range of cells. Is there a way to limit this formula to only changes within a specific tab? Thanks
Is it possible to select as a fresh start of searchable drop down list in every cells of the column? This method you have to select the empty value to make the value go back to the original which is empty in the D2 cell.
Neil, Fantastic videos and ideas. Thank you so much. I am trying to expand the capability of this drop down list into multiple tabs. Is there a way to use just the one list of data, but still be able to see it in the drop downs if I copy the tab? Even better if the user changes the tab's name?
I'd still like to know the answer to this for future reference, but what I did was include my list of data on the same tab as the drop down, so when I copy the tab, the name manager automatically, creates a new name that references the new tab.
Dear Neil, your video is very helpfull but for my drop down list is confusing. I have drop down list with categories, next to this list i have another list that is connected by the formula =indirect, and i need this to search in those items of that category. Thank you !!!
hey, the video was great. I really have started admiring you. Hundreds of other videos to achieve the same thing couldn't do it without venturing into VB. But you did it. Brave friend. Now one demand. Couldn't you provide us the same exercise file so that we could practice along with your video. We are not as smart as you are bhai.
Hai Neil, your video is great.. thanks you very much. But could you help me, if I used the shortcut key alt+down arrow, it show all the list not just the text I search. Is there any shortcut key for the drop down list?
Hi Neil, This is brilliant, thanks for recording and putting this onto You Tube. Ive managed to follow everything and get the drop downs to work effectively. One problem I have though.... When I protect the sheet the drop down list stops working even though I have not locket the lookup cell. any ideas? Jason
Hey Thanks for the video.. really helpful. Have got few questions. Is it possible, if yes, how can one format the Input Message tip pop up box, so as to e.g have particual lenght/height? and the position to be flexible as you scroll up or down?
Brilliant! Is there a way to keep the functionality after protecting the worksheet? I have locked certain cells and as a result the dropdown search wont work when sheet is protected. Any ideas? Thx!
Hi Neil, Can these functions be used in multiple worksheets or workbooks. Please confirm if there is a way it can be done from another sheet. Create a searchable drop down list, the same way a normal drop down list works from another sheet same workbook. Regards Frank
So I'm using this on an invoice, and inventory spread sheet. So it should be possible to auto fill a row of input referencing the 1 that is a match as a result of my word search. Thus auto filling the rest of the cells in a row by searching that column for the 1?
It should be relatively straightforward (if you've followed Neil's excellent guide all the way through, it sounds like what you want is far simpler) to look-up values in another table. You would lookup the selected search value, refer to the table where that information is held, and retrieve the relevant column of information. I use the search term to reduce a list of about 1000 items, but i then perform a vlookup to bring in other relevant information from another large data table
Great video, it really helped me..thank you One question with Data Validation I need to have validation also working to confirm user has selected from list and it is not user input. Please advice
Thank you. This is ALMOST exactly what I need... I[m trying to build a recipe costing template, that links the price of ingredients to the recipes as they're built, or as prices change over time. I have the price comparison sheet... that updates with the best price based on a few different suppliers... and now I can type in a few letters of an ingredient (Thank you so much... been struggling with that part for days), but how do I get the yellow cells information to link with additional information in the price comparison sheet? In these sense... On sheet A "Price List", I have used your formulas from this video and the previous one to add ingredients without having to scroll through the entire list... On Sheet A (Price List), the Item Name is column C. The Recipe Unit is Column AD, $/Recipe Unit is Column AE. and the Cheapest Supplier is Column AC. On sheet B (Recipe Template), Column C - Ingredient List (from Sheet A-Column C - Row vary depending on Ingredient Name), D - Quantity (To be entered manually), E - Unit (Linked to Sheet A - Column AD - Row ????(Whatever the ingredient row is), F - $/Unit (linked to Sheet A, Column AE, Row ????), G - Extended Cost (this one I can figure out hahaha), F - Best Supplier (Linked to Sheet A, Column AC, Row ????) any idea how what formula I would use to figure this out? EDIT: I just noticed you posted this video in 2013, and the last comment was 5 years ago... seriously though.... if anyone can help.... It would be greatly appreciated. Thank you for both of these videos, and for any extra help you might be able to give. Thanks, CM
Very good video, I have one question, I am trying to create a register with multiple columns and I would like to use multiple searchable drop down lists, with different values in each list. Have you any ideas how I can go about this?
Hi Neil great tool i have been using this method for some time. One issue i always run into is the #REF error when ever i want to delete my current list and build another. This happens everytime i change the data in Culumn G i get the #REF. If you delete the the data in culumn G and start a new list the drop down does not function correctly due to #REF, Any help would be fantastic.
Thank you for sharing this video. However whenever I type something anywhere else (outside the specified range), the list keeps getting refreshed. Is there a VBA code somebody has that can limit the refresh to the specified range only? thanks
Hi Neil Can the searchable function be used in multiple worksheets or workbooks. Please confirm if there is a way it can be done from another sheet. Create a searchable drop down list, the same way a normal drop down list works from another sheet same workbook or different workbook. Regards Frank
An interesting point. If you are typing a name and get no matches (and you think there should be one, you might wish you could pull down the complete drop down list like you would with a normal data validation list. You can get this in the searchable list cells by placing an asterisk (*) in the cell and click the down arrow.
Hi Neil, I tried your dropdown because I have a very large table over 2000 lines and each line has a dropdown, and the list for the dropdown also has over 500 customers listed. The way you proposed the searchable dropdown works but it is very temperamental It will work then knowing the data is in the dropdown list data, the company name does not populate the dropdown. Not sure if it is because I am using Office 365 the latest version of Excel. and it does not like some of the old formula any more. I really don't know. some of the newer formula need helper cells for each spill. This means i need a separate sheet to run the searchable dropdowns. this is not a good idea. Not sure if you can help.
Hi Neil. Really good videos. These have helped a lot. Also, the comments too. I like your presentation style but you seem to have stopped youtubing - that's a shame. You mention someone called Tim Perchanski (?). I'd like to visit his website/youtube but can't find him. Have I spelt his name correctly? Can you let me know how to access his site? Thanks.
Hi, this little series really helped me, thanks so much! Is there a way I can split this over two sheets? So, for example, I have everything but the drop-down on sheet 2 "pick lists" and the drop down in sheet 1 "data"?
+Courtney H In response to my own question (in case anybody else is wondering) I didn't want to do all of this on the data sheet and then have to hide it, so, instead, create a named range referring to that offset formula, but use external references within that formula. Not sure if this is the proper way but it worked for me. Eg. I now have a named range on sheet "Data" called "validation_list_sheets" which refers to: =OFFSET('Pick Lists'!$B$3,,,COUNTIF('Pick Lists'!$B$3:'Pick Lists'!$B:L,"?*"),) hope this helps somebody :)
can i export and use the same searchable drop-down menu in another worksheet in the same workbook without having to copy the entire data from the existing sheet. what i need to do is to use the dropdown menu and its data in another sheet.
Great instructions thank you. I have one question that would help speed up data entry: is there a way to show the drop down list without clicking the arrow? Usually Alt-down arrow does this, but it appears blank with your method. When entering hundreds of data entry items, avoiding the mouse and clicking little buttons would be fantastic. I have also seen combo boxes show live search suggestions without clicking the arrow. Any thoughts? Keep up the great work..
After typing your keyword(s) press Ctrl+Enter then Alt+DownArrow. I have also made a suggestion that makes the full list appear if you only press Alt+DownArrow (copied below): " To solve the last problem (5:50) one may do the following: (1) Select cell A1 (this is important) (2) Add the name "validation_list" and make it refer to the formula =IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101) (3) Change the data validation source to the formula =validation_list Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have: (1) changed the COUNTIF() function to the MAX() function (2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list. "
@@MohamedIbrahim-im5qs Hi I'm interested to find out more about this. But I can't achieve the desired outcome as per mentioned by following your instructions. Can you help me out by elaborating with a step by step guide? Thanks!
You are the Yoda of Excel. Thank you for part 1 and part 2 of this solution. I really appreciate your attention to detail! Your solution works across multiple sheets too, so If you place the lists on a separate sheet, you can have multiple "Searchable Drop Downs" on various sheets all referencing one "Lists" sheet (which you can hide). Brilliant!
This has been a godsend for me in setting up multiple projects (from a list of circa 500+ projects, multiple customers, and multiple resources and aiding calculations of resource utilization and revenue generation that much easier. Thanks Neil, top stuff!
I just wanted to say your 1st video and 2nd video was a life saver for me at work on a project i was working on!! you explain very well !! thank you
Thank you so much Neil. I created a separate data entry sheet and referenced the validation list on a separate sheet. It was a great help especially that the names to choose from are about a thousand names. Cheers!
Thank you so much for putting this together. This info, plus the additional info from part 1, will save me so much time. A quick side note, I started to experience excel not responding shortly after implementing this. But it was do to some of my other sells using index\match of an entire column. When you use reference an entire column ($A:$A) you are asking Excel to look at over a million rows. Keep this in mind if you run into excel not responding.
Great share Neil - The first video was good and then I needed exactly this to finish off the project. So glad I came across the first video. But then the second, as I thought I was stopped in my tracks again. Just wanted to say thank you :-)
Thank you Neil, excellent follow up... I can finish the project now or at least proceed to the next problem (lol).
For anyone interested I am using the function to search a list of foods to find their carbohydrate content. I can now enter a food type, ie potato, carrot, chicken etc, and display the total carb content of the meal. I will then use the information to tell me the units of insulin I require to treat my diabetic problem...
Thanks once again Neil, for a well made video...
Ok. this is golden. This "contents" of last modified cell - AWESOME.
Lovin' it Neil. I'll be looking up more of your vids in the future. Great style, very easy to follow.
Thanks a lot for the tutorials! I have been searching for a working solution for months.
Excel Ninja - I like the complement given to that guy... the tutorial was helpful. Thanks.
This just might be the neatest excel trick I came Upon!
truly amazing.
Already used =CELL in a few sheets, man, so useful.
Kudos
Thanks for that trick
Wow, what a fantastic result, brilliant. Not only cell reference but dynamic lists is new to me and always given me a headache having to use static named lists - really clever.
This was awesome. It really helped with the project I'm working on.
I also found, if, in the Data Validation box, you check if the value in the current cell isn't equal to the value in the cell pulling "contents", and if not, have it pull from the full list, it fixes the blank issue
For Example, in the Data Validation box of cell D5:
=IF($D5$D$2,$G$2#,$I$2#)
Now, this can be filled down and if you select any field that doesn't have the same value as the last thing you typed (which is in D2), then it will show the full name list.
If, however, you select a name, then go back to the box and pull down the drop-down, you will only see that name.
Also, I removed the returning of 0's with =IF(CELL("contents")="","",CELL("contents")), otherwise Null was returning 0
Great videos, just a quick addition that works for me to solve the empty dropdown if nothing is typed in.
Within the data validation box, I use the following IF statement:
=IF(D5="",G1:G22,OFFSET($I$2,,,COUNTIF($I:$I,"?*")))
In short, if nothing is typed, the full list is shown, however, if you type then the offset rule is triggered. (I used a named range for the full list for ease)
Hi Neil, this is a very clever trick, thank you for sharing.
To solve the last problem (5:50) one may do the following:
(1) Select cell A1 (this is important)
(2) Add the name "validation_list" and make it refer to the formula
=IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101)
(3) Change the data validation source to the formula
=validation_list
Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have:
(1) changed the COUNTIF() function to the MAX() function
(2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list.
OMFG YOU'RE A GENIUS! I've been doing so many sh*t instead.. you're golden never change
That was brilliant, just answered the question I asked earlier. to get around the no return, what I have done is finished my data with a space, so if I include a space I get my full list
Thanks again
I also thought about that but i ended up keeping my data as they are and modifying the formula which starts with if(isnumber(search... to something like if(or(cell("contents")=0,isnumber(search....
So far so good. Hope it wont crash
Neil - Tons of Shukriya (Thanks), my brother in law gave me one of his fancy demands which required the above solutions. Thanks again.
Thank you for this excellent video. I have been searching RUclips for this routine.
Thanks for posting this two tutorials. it's very helpful to me.
Very neat idea along with the first part of this video. Works like a charm. Thanks
THAT . IS . SIMPLY. BRILLIANT! You just saved me lots of effort for my work! thanks so much!
I finally got it to work and I am so happy...... Neil give yourself a pat on the back from me.... whoot....
Thanks a lot for your tutorial on this. Helps me a lot.I have multiple drop down list, each one with different reference and the list is long enough to make my excel lagging. Rather than using CELL, i prefer macro.
YOU ARE FANTASTIC. Thank you, these two videos were so damn helpful.
Very helpful, works perfectly for my calorie counter spreadsheet!
Hi Neil. Thank you very much for your help! This is VERY helpful!
Thank you a billion times, You are very smart and you really helped me out . thank you again.
Great tutorial Neil! Both Part 1 and 2
thank you!!! thank youuu!! i don't know this tutorial further for make list in table !! Very Helpful !!
Great videos Part 1 and 2, excellent work. Thanks Neil.
very useful and informative!!!!!! helped very much in my office work.... Thank you, neil....
Just contributing my 2 cents, To limit the search trigger to a specific column and row and to make the whole list visible when no text is entered initially to the dropdown list cells, or when you omit the text in those cells..
=IF(AND(CELL("col")=2, CELL("row")
How todo this in table? I wnat to check only one column on one table
This video along with part one was so helpful!!! I was able to create an awesome data gathering tool for my department!
Very helpful. If you type a space then hit the drop down box, you get the entire list (if you wanted that)
Absolutely Fantastic! Not only the content, but the style in which you teach us how to become better users. One small suggestion is you may want to copy the contents of the original dropdown and then do a special paste into the target range rather than recreate the data validation in the new range.
Thanks a Lot Niel You are wonderful trainer
Awesome solution, very grateful, kudos
You are the man... Thanks bro.. You saved my day..
cell function is amazing specially when you use it with no option for refreshment.
Neil, thank you very much! This is very helpful.
Works like a charm on my 2500+ record list
This was enormously helpful! Thank you!
hei neil, thank you so much, ur video is so help full
very very helpful. You make me happy man.
You da man! Thanks so much... can't wait for part 3!
Fantastic video! Thanks a lot
Fantastic ! You're a star....thanks a real lot.
Many thanks!!! Great video
This is incredibly helpful, thanks!
Thanks Neil. It is of great help
incredibly helpful - thanks very much indeed!
thank you so much sir! it's a big help to me ^_^
It was a great video on search option !!! How do use the Cell formula if the lists are in sheet 1 and data entry column and validation is in sheet 2.
Neil,
Thanks a ton for this!!!!!!!!!!!!!!!
Can you please share the excel sheet on cloud (google drive etc.) and share the link for it.
I was struggling to understand this and get it to work so ended up playing around with the whole sheet. I ended up adding the cell function into the 'if' in column a so =IF(ISNUMBER(SEARCH(CELL("contents"),B2)),MAX($A$1:A1)+1,0).
If you want to extend this to only trigger the search if the data is entered in a cell from a specific column (even from another sheet) add the following function to the master search cell :=IF(CELL("col")="";CELL(contents);"")Regards,David
Nice!
i'm trying that but i must be doing it wrong, pls help, heres what i have: =IF(CELL("col")=invoice!A:B;CELL(contents);"")
okay, i gave it another shot, but i just get ?NAME with this: =IF(CELL("address",X5),CELL(contents),"")
i think i got it: =IF(CELL("col",,CELL("contents"),"") i was trying it on the same worksheet but it wasn't working, had to switch worksheet i guess.. also i used a table for mine so it'll auto-add new dropdowns with tab at the end of the table, so mine looks like: =IF(CELL("col",Table2[TEST PARTS]),CELL("contents"),"")
I can't seem to figure this one out. Did you get it to work?
This work great, but it allows any typing to be added not only the drop down options. Is there a way to restrict to only the drop down options?
Great video instruction Neil thank you. I have this working across multiple sheets (to populate courses with students names - also fills in additional info in columns from lookup table). My problem is when I protect each worksheet the dropdown doesn't refresh to show the names (only the last cell content entered), if I type part of the name and press enter THEN click the dropdown it works. Anyway to do a refresh on protected sheet?
Dennis - How'd you get it to work on a different sheet? I"m getting an error when trying to make the data validation look at the offset formula from the other sheet. Says I cannot use unions or references in Data Validation formulas.
@@garrettlarance859 Use indirect
Great it works A1 but I have a scrip that move things around within the workbook and so the Cell("content") function is called every time and really put a toll on Excel. Is it possible to limit the range of the function of Cell("content") to a specific range. For example if there's a change only within A1:A100 ?
This is so wonderful. thank u so much!!!!
+Neil Firth Thank you for these great tutorial videos, they are very practical. My data entry columns are in another sheet and I cannot seem to generate the dynamic list as intended. Are you able to provide a sample file that contains the data used in the video so I can compare please?
Thanks so much. It's very helpful.
You have literally made my life a living hell.. no wait.. My boss has you have saved me.. I just want to thank you.. Id almost kiss you if I met you.. :D
These two parts have been super helpful! Thank you so much! Question though: Is there a way to either force-select or auto-complete the inputs in Column D? For instance, I want to make sure that the final input is a string that is equal to one of the names listed in Column G--in other words, can we put an error message if the user types Tiger and hits enter--because I want the actual input to be Tiger Woods. (I hope my question is clear).
Thanks a bunch mate! I'll make some peeps happy here :)
Hi Neil, Thanks a lot for part one and part two it wors perfectly! I Previously used INDIRECT as part of my drop down list, as the list was dependent on the contents of the cell next to the list. Is there a way of getting these to work together with the search function?
Thank you for posting this video (as well as Pt 1), it was very helpful.
Is there any way of using this searchable drop-down list while still utilizing an Error Alert function?
Hi Neil, your solution is very interesting thanks.
I was wondering if it is possible to oblige an operator to insert in the cell only the content of the searchable drop down list.
thanks
massimo
Hi Neil,
How do I get rid of circular reference error @ 3:00?
I liked the addition to this video of the =cell("contents") piece as I did want to use the data validation across a range of cells. Is there a way to limit this formula to only changes within a specific tab? Thanks
Is it possible to select as a fresh start of searchable drop down list in every cells of the column? This method you have to select the empty value to make the value go back to the original which is empty in the D2 cell.
Neil, Fantastic videos and ideas. Thank you so much. I am trying to expand the capability of this drop down list into multiple tabs. Is there a way to use just the one list of data, but still be able to see it in the drop downs if I copy the tab? Even better if the user changes the tab's name?
I'd still like to know the answer to this for future reference, but what I did was include my list of data on the same tab as the drop down, so when I copy the tab, the name manager automatically, creates a new name that references the new tab.
Dear Neil, your video is very helpfull but for my drop down list is confusing. I have drop down list with categories, next to this list i have another list that is connected by the formula =indirect, and i need this to search in those items of that category. Thank you !!!
hey, the video was great. I really have started admiring you. Hundreds of other videos to achieve the same thing couldn't do it without venturing into VB. But you did it. Brave friend.
Now one demand. Couldn't you provide us the same exercise file so that we could practice along with your video. We are not as smart as you are bhai.
Hai Neil, your video is great.. thanks you very much. But could you help me, if I used the shortcut key alt+down arrow, it show all the list not just the text I search. Is there any shortcut key for the drop down list?
Hi Neil,
This is brilliant, thanks for recording and putting this onto You Tube.
Ive managed to follow everything and get the drop downs to work effectively.
One problem I have though....
When I protect the sheet the drop down list stops working even though I have not locket the lookup cell.
any ideas?
Jason
+Jason Ridyard Hi. I have the exact same problem. Have you found a solution to this?
Hi Jim,
No I haven't unfortunately.
cheers
Jason
+Jason Ridyard but if you look further down in this thread, you'll find the solution, making object editable.
Hey Thanks for the video.. really helpful. Have got few questions. Is it possible, if yes, how can one format the Input Message tip pop up box, so as to e.g have particual lenght/height? and the position to be flexible as you scroll up or down?
this is such amazing, thank you so much :)
Brilliant! Is there a way to keep the functionality after protecting the worksheet? I have locked certain cells and as a result the dropdown search wont work when sheet is protected. Any ideas? Thx!
Hi Neil,
Can these functions be used in multiple worksheets or workbooks.
Please confirm if there is a way it can be done from another sheet. Create
a searchable drop down list, the same way a normal drop down list works from another sheet same workbook.
Regards
Frank
So I'm using this on an invoice, and inventory spread sheet. So it should be possible to auto fill a row of input referencing the 1 that is a match as a result of my word search. Thus auto filling the rest of the cells in a row by searching that column for the 1?
I would also like to know how to do this.
It should be relatively straightforward (if you've followed Neil's excellent guide all the way through, it sounds like what you want is far simpler) to look-up values in another table. You would lookup the selected search value, refer to the table where that information is held, and retrieve the relevant column of information. I use the search term to reduce a list of about 1000 items, but i then perform a vlookup to bring in other relevant information from another large data table
Great video, it really helped me..thank you
One question with Data Validation I need to have validation also working to confirm user has selected from list and it is not user input. Please advice
Thank you. This is ALMOST exactly what I need...
I[m trying to build a recipe costing template, that links the price of ingredients to the recipes as they're built, or as prices change over time. I have the price comparison sheet... that updates with the best price based on a few different suppliers... and now I can type in a few letters of an ingredient (Thank you so much... been struggling with that part for days), but how do I get the yellow cells information to link with additional information in the price comparison sheet?
In these sense... On sheet A "Price List", I have used your formulas from this video and the previous one to add ingredients without having to scroll through the entire list...
On Sheet A (Price List), the Item Name is column C. The Recipe Unit is Column AD, $/Recipe Unit is Column AE. and the Cheapest Supplier is Column AC.
On sheet B (Recipe Template), Column C - Ingredient List (from Sheet A-Column C - Row vary depending on Ingredient Name), D - Quantity (To be entered manually), E - Unit (Linked to Sheet A - Column AD - Row ????(Whatever the ingredient row is), F - $/Unit (linked to Sheet A, Column AE, Row ????), G - Extended Cost (this one I can figure out hahaha), F - Best Supplier (Linked to Sheet A, Column AC, Row ????)
any idea how what formula I would use to figure this out?
EDIT: I just noticed you posted this video in 2013, and the last comment was 5 years ago... seriously though.... if anyone can help.... It would be greatly appreciated.
Thank you for both of these videos, and for any extra help you might be able to give.
Thanks,
CM
Very good video, I have one question, I am trying to create a register with multiple columns and I would like to use multiple searchable drop down lists, with different values in each list. Have you any ideas how I can go about this?
Hi Neil great tool i have been using this method for some time. One issue i always run into is the #REF error when ever i want to delete my current list and build another. This happens everytime i change the data in Culumn G i get the #REF. If you delete the the data in culumn G and start a new list the drop down does not function correctly due to #REF, Any help would be fantastic.
Thank you sir very helpful
Thank you for sharing this video. However whenever I type something anywhere else (outside the specified range), the list keeps getting refreshed. Is there a VBA code somebody has that can limit the refresh to the specified range only? thanks
Hi Neil
Can the searchable function be used in multiple worksheets or workbooks.
Please confirm if there is a way it can be done from another sheet. Create
a searchable drop down list, the same way a normal drop down list works from another sheet same workbook or different workbook.
Regards
Frank
Thank you! Most helpful!
An interesting point. If you are typing a name and get no matches (and you think there should be one, you might wish you could pull down the complete drop down list like you would with a normal data validation list. You can get this in the searchable list cells by placing an asterisk (*) in the cell and click the down arrow.
This video saved me lots of time. Thank you. One question, can I insert a line into my list. I have tried to but get an error.
Hi Neil, I tried your dropdown because I have a very large table over 2000 lines and each line has a dropdown, and the list for the dropdown also has over 500 customers listed. The way you proposed the searchable dropdown works but it is very temperamental It will work then knowing the data is in the dropdown list data, the company name does not populate the dropdown. Not sure if it is because I am using Office 365 the latest version of Excel. and it does not like some of the old formula any more. I really don't know. some of the newer formula need helper cells for each spill. This means i need a separate sheet to run the searchable dropdowns. this is not a good idea. Not sure if you can help.
Hi Neil. Really good videos. These have helped a lot. Also, the comments too. I like your presentation style but you seem to have stopped youtubing - that's a shame. You mention someone called Tim Perchanski (?). I'd like to visit his website/youtube but can't find him. Have I spelt his name correctly? Can you let me know how to access his site? Thanks.
And that was my second question, thanks a lot!
Hi, this little series really helped me, thanks so much!
Is there a way I can split this over two sheets? So, for example, I have everything but the drop-down on sheet 2 "pick lists" and the drop down in sheet 1 "data"?
+Courtney H
In response to my own question (in case anybody else is wondering)
I didn't want to do all of this on the data sheet and then have to hide it, so, instead, create a named range referring to that offset formula, but use external references within that formula.
Not sure if this is the proper way but it worked for me.
Eg. I now have a named range on sheet "Data" called "validation_list_sheets" which refers to:
=OFFSET('Pick Lists'!$B$3,,,COUNTIF('Pick Lists'!$B$3:'Pick Lists'!$B:L,"?*"),)
hope this helps somebody :)
+Courtney H NB: I do now have a really long drop-down list though, if I don't type anything at first.
can i export and use the same searchable drop-down menu in another worksheet in the same workbook without having to copy the entire data from the existing sheet. what i need to do is to use the dropdown menu and its data in another sheet.
Can add a combobox or convert all normal drop down validation list to a combobox list will be more easier and fun
Great instructions thank you. I have one question that would help speed up data entry: is there a way to show the drop down list without clicking the arrow? Usually Alt-down arrow does this, but it appears blank with your method. When entering hundreds of data entry items, avoiding the mouse and clicking little buttons would be fantastic. I have also seen combo boxes show live search suggestions without clicking the arrow.
Any thoughts?
Keep up the great work..
After typing your keyword(s) press Ctrl+Enter then Alt+DownArrow. I have also made a suggestion that makes the full list appear if you only press Alt+DownArrow (copied below):
"
To solve the last problem (5:50) one may do the following:
(1) Select cell A1 (this is important)
(2) Add the name "validation_list" and make it refer to the formula
=IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101)
(3) Change the data validation source to the formula
=validation_list
Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have:
(1) changed the COUNTIF() function to the MAX() function
(2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list.
"
@@MohamedIbrahim-im5qs Hi I'm interested to find out more about this. But I can't achieve the desired outcome as per mentioned by following your instructions. Can you help me out by elaborating with a step by step guide? Thanks!